Streamlining Laboratory Reagent and Consumable Orders

Japanese version:

Achievements

In the course of laboratory activities, we frequently need to order various reagents and consumables. This process can be quite tedious. To address this issue, I've written a Python script to simplify the process, which I'd like to introduce.

First, in our laboratory, we use an Excel file stored on a NAS (Network Attached Storage) to record necessary items. This file is set up to automatically populate product details using XLOOKUP when a product code is entered. Moreover, it automatically selects the cheapest supplier.

The process works as follows: if the status column is blank, an order email is sent; if it's marked for quotation, a quote request is sent to multiple suppliers simultaneously.

Script Overview

The Python script performs the following functions:

1. Reads order data from the Excel file

2. Groups orders by supplier

3. Further groups orders by person responsible for ordering

4. Generates and sends emails for each group

5. Handles quote requests similarly

6. Updates the status in the Excel file post-processing

This script automates and streamlines the ordering process, saving time and reducing the administrative burden on laboratory staff.

Implementation

Environment Setup

Required packages:

conda install pandas openpyxl python-dotenv

Script

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

# Get current date
current_year = datetime.now().year
current_month = datetime.now().month

# Define file name
file_name = f"{current_year}{current_month:02d}_order_sheet.xlsm"

# Determine path based on operating system
if os.name == 'nt':  # Windows
    excel_file_path = Path(rf'\\server\shared_folder\orders\{current_year}\{file_name}')
elif os.name == 'posix':  # macOS
    excel_file_path = Path(f'/Volumes/shared_folder/orders/{current_year}/{file_name}')
else:
    raise EnvironmentError("Unsupported Operating System")

# Mapping of suppliers to email addresses
order_to_email = {
    'Company A': {'to': 'orderA@example.com', 'cc': 'ccA@example.com, ccB@example.com'},
    'Company B': {'to': 'orderB@example.com', 'cc': 'ccA@example.com'},
    'Company C': {'to': 'orderC@example.com', 'cc': 'ccA@example.com'},
    # ... similarly for other companies
}

# Mapping of suppliers to contact names
order_to_name = {
    'Company A': 'Mr. A',
    'Company B': 'Ms. B',
    'Company C': 'Dr. C',
    # ... similarly for other companies
}

# Email addresses for quotations
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']

# Load environment variables from .env file
load_dotenv()

# Get SMTP server settings from environment variables
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 = []

    # Group by orderer and person responsible for ordering if it's a quote request
    groupby_columns = ['Orderer', 'Responsible Person'] if is_quote else ['Supplier', 'Orderer', 'Responsible Person']

    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  # Email for this group has already been sent

        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 = 'Sir/Madam' if is_quote else order_to_name.get(group_key[0], 'Sir/Madam')

        # Set additional CC addresses
        additional_cc = set()
        for person in group_key if is_quote else group_key[1:]:
            if person == 'Special Person A':
                additional_cc.add('specialA@example.com')
            elif person == 'Special Person B':
                additional_cc.add('specialB@example.com')
            elif person == 'Special Person C':
                additional_cc.add('specialC@example.com')
        
        # Update CC addresses
        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']))  # Remove duplicates

        # Create email object
        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'] = "Request for Quotation"
        else:
            msg['Subject'] = "Order Request"

        # Create email body
        if is_quote:
            body = f"Dear {recipient_name},\n\nWe kindly request a quotation for the following items:\n\n"
            body += "If possible, please provide the quotation in the email body using the following format:\n"
            body += "Product Name:\n"
            body += "Company Name:\n"
            body += "Product Number:\n"
            body += "List Price:\n"
            body += "Offered Price:\n\n"
        else:
            body = f"Dear {recipient_name},\n\nWe would like to place an order for the following items. Please notify us if there are any pricing discrepancies.\n\n"

        for i, row in enumerate(group.itertuples(), 1):
            quantity = int(row.Quantity) if isinstance(row.Quantity, float) and row.Quantity.is_integer() else row.Quantity
            body += f"{i}\n"
            body += f"Product Name: {row.ProductName}\n"
            body += f"Company: {row.Company}\n"
            body += f"Catalog Number: {row.CatalogNumber}\n"
            if not is_quote:
                body += f"Quantity: {quantity}\n"
                body += f"Unit Price: {row.UnitPrice}\n"
                body += f"Delivery Address: {row.Orderer}\n"
                body += f"Budget: {'TBD' if pd.isna(row.Budget) else row.Budget}\n"
            body += "\n"
        
        msg.attach(MIMEText(body, 'plain'))

        # Send email
        try:
            server.send_message(msg)
            print(f"Email sent successfully: {'Quote' if is_quote else 'Order'} - {email_key}")
            
            # Update status
            new_status = 'Quoted' if is_quote else 'Ordered'
            for index in group.index:
                updated_rows.append({'index': index, 'new_status': new_status})
            
        except Exception as e:
            print(f"Failed to send email: {'Quote' if is_quote else 'Order'} - {email_key}, Error: {str(e)}")

        # Record sent email
        sent_emails.add(email_key)

    return updated_rows

# Main process
if __name__ == "__main__":
    # Read Excel file
    df = pd.read_excel(excel_file_path)

    # Extract rows where 'Catalog Number' and 'Responsible Person' exist, and 'Status' is blank
    df_filtered = df[df['CatalogNumber'].notna() & df['Status'].isna() & df['ResponsiblePerson'].notna()]

    # Extract rows where 'Status' is "Quote"
    df_quote = df[df['Status'] == 'Quote']

    # Connect to SMTP server
    try:
        server = smtplib.SMTP(smtp_server, smtp_port)
        server.starttls()
        server.login(smtp_user, smtp_password)

        all_updated_rows = []

        # Order processing (only for rows with blank status)
        if not df_filtered.empty:
            print("Starting order processing.")
            updated_rows = send_emails(df_filtered)
            all_updated_rows.extend(updated_rows)
        else:
            print("No data for order processing.")

        # Quote processing
        if not df_quote.empty:
            print("Starting quote processing.")
            updated_rows = send_emails(df_quote, is_quote=True)
            all_updated_rows.extend(updated_rows)
        else:
            print("No data for quote processing.")

    except Exception as e:
        print(f"SMTP server connection error: {str(e)}")

    finally:
        # Close server
        server.quit()

    # Reopen the original file using openpyxl and update status
    try:
        wb = load_workbook(excel_file_path, keep_vba=True)
        ws = wb.active
        
        # Get index of 'Status' column
        status_column = next(i for i, col in enumerate(ws[1], 1) if col.value == 'Status')
        
        # Update status in the original dataframe
        for row in all_updated_rows:
            df.at[row['index'], 'Status'] = row['new_status']
            ws.cell(row=row['index'] + 2, column=status_column, value=row['new_status'])

        # Save file
        wb.save(excel_file_path)
        print("Excel file update completed.")
    except Exception as e:
        print(f"An error occurred while updating the Excel file: {str(e)}")

    # Check updated data
    updated_df = pd.read_excel(excel_file_path)
    print(updated_df[['CatalogNumber', 'ResponsiblePerson', 'Status']].head(10))

.env

# SMTP server settings
SMTP_SERVER=smtp.example.com
SMTP_PORT=587
SMTP_USER=your_username@example.com
SMTP_PASSWORD=your_secure_password_here

Conclusion

This automation system now enables us to automatically request quotes from all our regular suppliers.

Furthermore, order emails are automatically sent to suppliers, with the ordering faculty members CCed for confirmation, providing an additional layer of assurance.

It is our hope that this system will allow more time to be devoted to educational and research activities, leading to increased satisfaction and productivity.

We have implemented various other systems as well, which we will introduce in future posts.

If you have any questions or suggestions for improvement, please feel free to leave a comment below.