Slack Bot を活用した研究室の出席管理システムを作ろう

mkdir ~/opt/labin
cd ~/opt/labin
python -m venv venv
source venv/bin/activate
pip install slack-bolt slack-sdk schedule pandas matplotlib seaborn japanize-matplotlib

import os
import sqlite3
import time
import schedule
import threading
import pandas as pd
import matplotlib.pyplot as plt
import japanize_matplotlib
import seaborn as sns
from datetime import datetime, timedelta
from slack_bolt import App
from slack_bolt.adapter.socket_mode import SocketModeHandler
from slack_sdk.errors import SlackApiError
from slack_sdk import WebClient

# 環境変数からSlackのチャンネルIDとトークン情報を取得
CHANNEL_ID = os.getenv('CHANNEL_ID')
BOT_USER_OAUTH_TOKEN = os.getenv('BOT_USER_OAUTH_TOKEN')
APP_LEVEL_TOKEN = os.getenv('APP_LEVEL_TOKEN')

# リアクションに使う絵文字
CHECKIN_EMOJI = 'wave'

# Slack Boltアプリのインスタンス作成
app = App(token=BOT_USER_OAUTH_TOKEN)
client = WebClient(token=BOT_USER_OAUTH_TOKEN)

# データベース接続とテーブル作成
conn = sqlite3.connect("labin.db", check_same_thread=False)
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS attendance (user_id TEXT, date TEXT, time TEXT)''')
conn.commit()

def send_attendance_message():
    try:
        response = app.client.chat_postMessage(
            channel=CHANNEL_ID,
            text=f"おはよう！ラボインしたら :{CHECKIN_EMOJI}: でリアクション"
        )
        app.client.reactions_add(
            channel=response['channel'],
            timestamp=response['ts'],
            name=CHECKIN_EMOJI
        )
    except SlackApiError as e:
        print(f"Error sending message: {e.response['error']}")

@app.event("reaction_added")
def handle_reaction_added(event, say):
    if event["reaction"] == CHECKIN_EMOJI:
        record_attendance(event["user"])

def record_attendance(user_id):
    date, checkin_time = time.strftime("%Y-%m-%d"), time.strftime("%H:%M:%S")
    c.execute("SELECT * FROM attendance WHERE user_id = ? AND date = ?", (user_id, date))
    if c.fetchone() is None:
        c.execute("INSERT INTO attendance (user_id, date, time) VALUES (?, ?, ?)", (user_id, date, checkin_time))
        conn.commit()
        print(f"Recorded attendance for user {user_id} at {checkin_time}")

def post_attendance_heatmap():
    end_date = datetime.today()
    start_date = end_date - timedelta(days=29)
    date_range = pd.date_range(start=start_date, end=end_date)
    date_labels = date_range.strftime("%m-%d")
    
    attendance_query = "SELECT user_id, date FROM attendance WHERE date BETWEEN ? AND ?"
    attendance_df = pd.read_sql_query(attendance_query, conn, params=(start_date.strftime("%Y-%m-%d"), end_date.strftime("%Y-%m-%d")))

    members_df = pd.read_csv("members.csv", header=None, names=["user_id", "fullname"])
    attendance_df = members_df.merge(attendance_df, on="user_id", how="left")
    attendance_df["date"] = pd.Categorical(attendance_df["date"], categories=date_range.strftime("%Y-%m-%d"), ordered=True)

    attendance_pivot = attendance_df.pivot_table(index="fullname", columns="date", aggfunc="size", fill_value=0, observed=False)
    attendance_pivot = attendance_pivot.reindex(index=members_df["fullname"], fill_value=0)

    plt.figure(figsize=(8, 8))
    sns.heatmap(attendance_pivot, cmap="Blues", cbar=False, linewidths=1)
    plt.xticks(ticks=range(len(date_labels)), labels=date_labels, rotation=45)
    plt.tight_layout()
    plt.savefig("labin.png")
    plt.close()

    try:
        client.files_upload_v2(
            channels=CHANNEL_ID,
            file="labin.png",
            title="過去30日間のラボイン",
            initial_comment="おつかれ！ :white_flower:"
        )
        print("Attendance heatmaps were posted on Slack.")
    except SlackApiError as e:
        print(f"Error uploading file: {e.response['error']}")

def run_schedule():
    while True:
        schedule.run_pending()
        time.sleep(1)

# スケジュール設定
schedule.every().day.at("06:00").do(send_attendance_message)
schedule.every().day.at("18:00").do(post_attendance_heatmap)

if __name__ == "__main__":
    threading.Thread(target=run_schedule).start()
    handler = SocketModeHandler(app, APP_LEVEL_TOKEN)
    handler.start()

[Unit]
Description=LabIn

[Service]
WorkingDirectory=/home/yamnor/opt/labin
ExecStart=/home/yamnor/opt/labin/venv/bin/python labin.py
Restart=always

[Install]
WantedBy=multi-user.target

sudo systemctl start labin
sudo systemctl enable labin