研究室の試薬や消耗品の発注を楽にする

English version:

実現したこと

研究室の活動の中で試薬や消耗品など色々と日々、発注しなければなりません。これがまためんどくさいですね。Pythonで楽にするコードを書きましたので紹介します。

まず、研究室ではNASに下記のようなエクセルファイルに必要なものを書いて貰って居ます。これはXLOOKUPを使って製品コードを入れると自動で反映されるようになっています。しかも、安い代理店が自動で選択されます。

発注用エクセルファイル

ステータスカラムが空欄なら手配するメール、見積なら見積をまとめて複数の代理店にメールでという感じです。

スクリプトの概要

• Excelファイルから発注データを読み込む

• 発注先ごとにグループ化する

• 発注先毎にグループ化されたものからさらに、発注責任者毎にグループ化し、グループ毎にメールを作成し送信する

• 見積もり依頼のメールも同様のロジックでグループ化し、送信する

• 処理後、Excelファイルのステータスを更新する

環境構築

必要なものをインストール

conda install pandas openpyxl python-dotenv

スクリプト

import os
import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from openpyxl import load_workbook
from pathlib import Path
from datetime import datetime
from collections import defaultdict
from dotenv import load_dotenv

# 日付を取得
current_year = datetime.now().year
current_month = datetime.now().month

# ファイル名を定義
file_name = f"{current_year}{current_month:02d}_発注表.xlsm"

# 実行しているOSによってパスを決定
if os.name == 'nt':  # Windowsの場合
    excel_file_path = Path(rf'\\server\shared_folder\注文書\{current_year}年\{file_name}')
elif os.name == 'posix':  # macOSの場合
    excel_file_path = Path(f'/Volumes/shared_folder/注文書/{current_year}年/{file_name}')
else:
    raise EnvironmentError("Unsupported Operating System")

# 発注先とメールアドレスのマッピング
order_to_email = {
    '会社A': {'to': 'orderA@example.com', 'cc': 'ccA@example.com, ccB@example.com'},
    '会社B': {'to': 'orderB@example.com', 'cc': 'ccA@example.com'},
    '会社C': {'to': 'orderC@example.com', 'cc': 'ccA@example.com'},
    # ... 他の会社も同様に
}

# 発注先と名前のマッピング
order_to_name = {
    '会社A': '担当者A様',
    '会社B': '担当者B様',
    '会社C': '担当者C様',
    # ... 他の会社も同様に
}

# 送信先のメールアドレス（見積用）
quote_email_to = 'quote@example.com'
quote_email_cc = ['cc1@example.com', 'cc2@example.com']
quote_email_bcc = ['bcc1@example.com', 'bcc2@example.com', 'bcc3@example.com']

# .envファイルから環境変数を読み込む
load_dotenv()

# SMTPサーバー設定を環境変数から取得
smtp_server = os.getenv('SMTP_SERVER')
smtp_port = int(os.getenv('SMTP_PORT'))
smtp_user = os.getenv('SMTP_USER')
smtp_password = os.getenv('SMTP_PASSWORD')

def send_emails(df, is_quote=False):
    sent_emails = set()
    updated_rows = []

    # 見積もりの場合は発注者と発注責任者でグループ化
    groupby_columns = ['発注者', '発注責任者'] if is_quote else ['発注先', '発注者', '発注責任者']

    for group_key, group in df.groupby(groupby_columns):
        group_key = tuple(group_key) if isinstance(group_key, tuple) else (group_key,)
        
        email_key = group_key
        if email_key in sent_emails:
            continue  # このグループ向けのメールは既に送信済み

        if is_quote:
            email_info = {'to': quote_email_to, 'cc': quote_email_cc.copy(), 'bcc': quote_email_bcc}
        else:
            order_to = group_key[0]
            email_info = order_to_email.get(order_to, {'to': 'default@example.com', 'cc': ''}).copy()

        recipient_name = '担当者様' if is_quote else order_to_name.get(group_key[0], '担当者様')

        # 追加のCCアドレスを設定
        additional_cc = set()
        for person in group_key if is_quote else group_key[1:]:
            if person == '特別担当者A':
                additional_cc.add('specialA@example.com')
            elif person == '特別担当者B':
                additional_cc.add('specialB@example.com')
            elif person == '特別担当者C':
                additional_cc.add('specialC@example.com')
        
        # CCアドレスを更新
        if is_quote:
            email_info['cc'].extend(additional_cc)
        else:
            email_info['cc'] = email_info['cc'].split(', ') + list(additional_cc)
        email_info['cc'] = list(set(email_info['cc']))  # 重複を削除

        # メールオブジェクトを作成
        msg = MIMEMultipart()
        msg['From'] = smtp_user
        msg['To'] = email_info['to']
        msg['Cc'] = ', '.join(email_info['cc'])
        if is_quote:
            msg['Bcc'] = ', '.join(email_info['bcc'])
            msg['Subject'] = f"見積のお願い"
        else:
            msg['Subject'] = f"手配のお願い"

        # メール本文の作成
        if is_quote:
            body = f"お世話になっております。{recipient_name}\n下記の見積をお願い致します。\n\n"
            body += "可能であれば見積もりはメール本文にベタ打ちで、下記のような形式で返信頂けますでしょうか？\n"
            body += "製品名：\n"
            body += "会社名：\n"
            body += "品番：\n"
            body += "定価：\n"
            body += "納入価：\n\n"
        else:
            body = f"{recipient_name}\n\nお世話になっております。\n下記の手配をお願いします。\n価格に誤りがある場合はご指摘頂けると助かります。\n\n"

        for i, row in enumerate(group.itertuples(), 1):
            quantity = int(row.数量) if isinstance(row.数量, float) and row.数量.is_integer() else row.数量
            body += f"{i}\n"
            body += f"品名：{row.商品名}\n"
            body += f"会社：{row.試薬会社}\n"
            body += f"カタログ番号：{row.カタログ番号}\n"
            if not is_quote:
                body += f"数量：{quantity}\n"
                body += f"納入価：{row.納入価}\n"
                body += f"納品先：{row.発注者}\n"
                body += f"予算：{'未定' if pd.isna(row.予算) else row.予算}\n"
            body += "\n"
        
        msg.attach(MIMEText(body, 'plain'))

        # メールを送信
        try:
            server.send_message(msg)
            print(f"メール送信成功: {'見積' if is_quote else '発注'} - {email_key}")
            
            # ステータスを更新
            new_status = '見積中' if is_quote else '手配済'
            for index in group.index:
                updated_rows.append({'index': index, 'new_status': new_status})
            
        except Exception as e:
            print(f"メール送信失敗: {'見積' if is_quote else '発注'} - {email_key}, エラー: {str(e)}")

        # 送信済みメールを記録
        sent_emails.add(email_key)

    return updated_rows

# メイン処理
if __name__ == "__main__":
    # Excelファイルを読み込む
    df = pd.read_excel(excel_file_path)

    # 'カタログ番号'と'発注責任者'が存在し、'ステータス'が空欄の行を抽出
    df_filtered = df[df['カタログ番号'].notna() & df['ステータス'].isna() & df['発注責任者'].notna()]

    # 'ステータス'が「見積」の行を抽出
    df_quote = df[df['ステータス'] == '見積']

    # SMTPサーバーに接続
    try:
        server = smtplib.SMTP(smtp_server, smtp_port)
        server.starttls()
        server.login(smtp_user, smtp_password)

        all_updated_rows = []

        # 発注処理（ステータスが空欄の行のみ）
        if not df_filtered.empty:
            print("発注処理を開始します。")
            updated_rows = send_emails(df_filtered)
            all_updated_rows.extend(updated_rows)
        else:
            print("発注対象のデータがありません。")

        # 見積処理
        if not df_quote.empty:
            print("見積処理を開始します。")
            updated_rows = send_emails(df_quote, is_quote=True)
            all_updated_rows.extend(updated_rows)
        else:
            print("見積対象のデータがありません。")

    except Exception as e:
        print(f"SMTPサーバー接続エラー: {str(e)}")

    finally:
        # サーバーを閉じる
        server.quit()

    # openpyxlを使用して元のファイルを再度開き、ステータスを更新
    try:
        wb = load_workbook(excel_file_path, keep_vba=True)
        ws = wb.active
        
        # 'ステータス'列のインデックスを取得
        status_column = next(i for i, col in enumerate(ws[1], 1) if col.value == 'ステータス')
        
        # 更新されたデータを元のデータフレームに反映
        for row in all_updated_rows:
            df.at[row['index'], 'ステータス'] = row['new_status']
            ws.cell(row=row['index'] + 2, column=status_column, value=row['new_status'])

        # ファイルを保存
        wb.save(excel_file_path)
        print("Excelファイルの更新が完了しました。")
    except Exception as e:
        print(f"Excelファイルの更新中にエラーが発生しました: {str(e)}")

    # 更新後のデータを確認
    updated_df = pd.read_excel(excel_file_path)
    print(updated_df[['カタログ番号', '発注責任者', 'ステータス']].head(10))

.env

# SMTPサーバー設定
SMTP_SERVER=smtp.example.com
SMTP_PORT=587
SMTP_USER=your_username@example.com
SMTP_PASSWORD=your_secure_password_here

まとめ

これで、出入りのある代理店全部に自動的に見積を依頼できます。

さらに、発注のメールも自動的に代理店さんに飛び、注文した教員にもCCで連絡が行くので安心です。

これで教育・研究活動に時間が使えるようになりハッピーになると良いですね。

他にも色々と仕組みを動かしていますので、都度紹介します！

質問や改善点があれば、ぜひコメントで教えてください。