auto_scheduler.py

import pandas as pd
import numpy as np
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.cell.cell import MergedCell
import openpyxl
from datetime import datetime, timedelta
import os
import platform
from pathlib import Path
import glob
import time
import sys

class AutoScheduler:
    def __init__(self):
        self.schedule = None
        self.pickup_orders = None
        self.dropoff_orders = None
        # 初始化需要排除的地点列表
        self.excluded_locations = ['博多駅', '成田机场', '福岡空港']
        # 获取下载文件夹路径
        self.downloads_path = self._get_downloads_path()

    def _get_downloads_path(self):
        """获取系统下载文件夹路径"""
        if platform.system() == 'Windows':
            import winreg
            sub_key = r'SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders'
            downloads_guid = '{374DE290-123F-4565-9164-39C4925E467B}'
            with winreg.OpenKey(winreg.HKEY_CURRENT_USER, sub_key) as key:
                downloads_path = winreg.QueryValueEx(key, downloads_guid)[0]
        else:  # macOS
            downloads_path = str(Path.home() / 'Downloads')
        return downloads_path

    def _find_excel_files(self):
        """在下载文件夹中查找Excel文件"""
        excel_files = []
        # 获取所有以order_export开头的Excel文件
        patterns = ['order_export*.xlsx', 'order_export*.xls']
        for pattern in patterns:
            excel_files.extend(glob.glob(os.path.join(self.downloads_path, pattern)))
        
        # 按文件修改时间排序，最新的文件在前
        excel_files.sort(key=lambda x: os.path.getmtime(x), reverse=True)
        return excel_files

    def _is_pickup_file(self, file_path):
        """判断是否为租车订单文件"""
        try:
            df = pd.read_excel(file_path)
            if 'Pick-up time (local)' not in df.columns:
                return False
            tomorrow = datetime.now().date() + timedelta(days=1)
            pickup_times = pd.to_datetime(df['Pick-up time (local)']).dt.date
            return any(time == tomorrow for time in pickup_times)
        except:
            return False

    def _is_dropoff_file(self, file_path):
        """判断是否为还车订单文件"""
        try:
            df = pd.read_excel(file_path)
            if 'Drop-off time (local)' not in df.columns:
                return False
            tomorrow = datetime.now().date() + timedelta(days=1)
            dropoff_times = pd.to_datetime(df['Drop-off time (local)']).dt.date
            # 检查是否包含明天的还车时间
            return any(time == tomorrow for time in dropoff_times)
        except Exception as e:
            print(f"读取还车文件时出错：{str(e)}")
            return False

    def find_order_files(self):
        """查找租车和还车订单文件"""
        excel_files = self._find_excel_files()
        pickup_file = None
        dropoff_file = None

        # 只检查当天创建的文件
        today = datetime.now().date()
        for file_path in excel_files:
            file_time = datetime.fromtimestamp(os.path.getctime(file_path)).date()
            if file_time == today:
                if not pickup_file and self._is_pickup_file(file_path):
                    pickup_file = file_path
                if not dropoff_file and self._is_dropoff_file(file_path):
                    dropoff_file = file_path
                if pickup_file and dropoff_file:
                    break

        return pickup_file, dropoff_file
    
    def set_excluded_locations(self, locations):
        """设置需要排除的地点列表"""
        self.excluded_locations = locations
    
    def load_orders(self, pickup_file, dropoff_file):
        """加载租车和还车订单"""
        # 读取租车订单
        self.pickup_orders = pd.read_excel(pickup_file)
        self.pickup_orders['type'] = '租车'
        self.pickup_orders['time'] = pd.to_datetime(self.pickup_orders['Pick-up time (local)']).dt.time
        self.pickup_orders['location'] = self.pickup_orders['Pick-up location']
        self.pickup_orders['order_id'] = self.pickup_orders['Klook booking reference ID']
        self.pickup_orders['rental_days'] = self.pickup_orders['Duration (days)']
        self.pickup_orders['vehicle'] = self.pickup_orders['Vehicle']
        self.pickup_orders['customer_name'] = self.pickup_orders['Customer name']
        self.pickup_orders['customer_phone'] = self.pickup_orders['Customer phone number']
        self.pickup_orders['customer_email'] = self.pickup_orders['Customer email']
        self.pickup_orders['additional_services'] = self.pickup_orders['Additional services']
        self.pickup_orders['insurance'] = self.pickup_orders['Insurance']
        
        # 读取还车订单
        self.dropoff_orders = pd.read_excel(dropoff_file)
        self.dropoff_orders['type'] = '还车'
        self.dropoff_orders['time'] = pd.to_datetime(self.dropoff_orders['Drop-off time (local)']).dt.time
        self.dropoff_orders['location'] = self.dropoff_orders['Drop-off location']
        self.dropoff_orders['order_id'] = self.dropoff_orders['Klook booking reference ID']
        self.dropoff_orders['rental_days'] = self.dropoff_orders['Duration (days)']
        self.dropoff_orders['vehicle'] = self.dropoff_orders['Vehicle']
        self.dropoff_orders['customer_name'] = self.dropoff_orders['Customer name']
        self.dropoff_orders['customer_phone'] = self.dropoff_orders['Customer phone number']
        self.dropoff_orders['customer_email'] = self.dropoff_orders['Customer email']
        self.dropoff_orders['additional_services'] = self.dropoff_orders['Additional services']
        self.dropoff_orders['insurance'] = self.dropoff_orders['Insurance']

    def generate_schedule(self, start_date, days):
        """生成排班表"""
        if self.pickup_orders is None or self.dropoff_orders is None:
            raise ValueError("请先加载租车和还车订单")
        
        # 初始化排班表
        self.schedule = []
        
        # 添加所有租车订单
        for _, order in self.pickup_orders.iterrows():
            pickup_time = pd.to_datetime(order['Pick-up time (local)'])
            self.schedule.append({
                'date': pickup_time.date(),
                'time': pickup_time.time(),
                'type': order['type'],
                'order_id': order['order_id'],
                'staff': ''
            })
        
        # 添加所有还车订单
        for _, order in self.dropoff_orders.iterrows():
            dropoff_time = pd.to_datetime(order['Drop-off time (local)'])
            self.schedule.append({
                'date': dropoff_time.date(),
                'time': dropoff_time.time(),
                'type': order['type'],
                'order_id': order['order_id'],
                'staff': ''
            })
    
    def export_to_excel(self, filename):
        """导出排班表到Excel"""
        if self.schedule is None:
            raise ValueError("请先生成排班表")
        
        wb = Workbook()
        ws = wb.active
        ws.title = "排班表"
        
        # 获取所有订单并按地址和时间排序
        all_orders = []
        for item in self.schedule:
            order_data = self.pickup_orders.loc[self.pickup_orders['order_id'] == item['order_id']].iloc[0] if item['type'] == '租车' else \
                       self.dropoff_orders.loc[self.dropoff_orders['order_id'] == item['order_id']].iloc[0]
            # 只添加不在排除地点列表中的订单
            if order_data['location'] not in self.excluded_locations:
                all_orders.append((item, order_data))
        
        # 计算当天的租车和还车数量（基于实际显示的数据）
        tomorrow = datetime.now().date() + timedelta(days=1)
        # 修改：直接统计最终输出数据中的租还数量
        pickup_count = len([item for item, order_data in all_orders if item['type'] == '租车' and order_data['location'] not in self.excluded_locations])
        dropoff_count = len([item for item, order_data in all_orders if item['type'] == '还车' and order_data['location'] not in self.excluded_locations])
        
        # 添加标题行
        title = f"{tomorrow.strftime('%Y-%m-%d')} 排班表 租({pickup_count})还({dropoff_count})"
        ws.merge_cells('A1:M1')
        ws['A1'] = title
        
        # 设置标题样式
        title_font = Font(bold=True, size=14)
        ws['A1'].font = title_font
        ws['A1'].alignment = Alignment(horizontal='center')
        
        # 添加表头
        headers = ['人员', '租还', '订单号', '租还时间', '租还地址', '天数', '车型', '客人姓名', '客人电话', '客人邮箱', '租还时间', '租还地址', '附加设备']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=2, column=col)
            cell.value = header
            cell.fill = PatternFill(start_color='CCCCCC', end_color='CCCCCC', fill_type='solid')
            cell.font = Font(bold=True)
        

        
        # 设置标题样式
        title_font = Font(bold=True, size=14)
        ws['A1'].font = title_font
        ws['A1'].alignment = Alignment(horizontal='center')
        
        # 添加表头
        headers = ['人员', '租还', '订单号', '租还时间', '租还地址', '天数', '车型', '客人姓名', '客人电话', '客人邮箱', '租还时间', '租还地址', '附加设备']
        for col, header in enumerate(headers, 1):
            cell = ws.cell(row=2, column=col)
            cell.value = header
            cell.fill = PatternFill(start_color='CCCCCC', end_color='CCCCCC', fill_type='solid')
            cell.font = Font(bold=True)
        
        # 按地址分组并按时间排序
        sorted_orders = {}
        for item, order_data in all_orders:
            location = order_data['location']
            if location not in sorted_orders:
                sorted_orders[location] = []
            sorted_orders[location].append((item, order_data))
        
        # 对每个地址组内的订单按时间排序
        for location in sorted_orders:
            sorted_orders[location].sort(key=lambda x: x[0]['time'])
        
        # 按地址顺序写入数据，不同地址之间添加空行
        current_row = 3
        for location in sorted(sorted_orders.keys()):
            for item, order in sorted_orders[location]:
                # 检查是否为需要排除的地点
                if order['location'] in self.excluded_locations:
                    continue
                # 合并Additional services和Insurance信息到附加设备列
                additional_services = str(order['additional_services']) if pd.notna(order['additional_services']) else ''
                insurance = str(order['insurance']) if pd.notna(order['insurance']) else ''
                additional_equipment = []
                
                # 检查Additional services中的设备
                if '雪地胎' in additional_services:
                    additional_equipment.append('雪胎')
                if 'ETC卡' in additional_services:
                    additional_equipment.append('ETC卡')
                if '儿童座椅' in additional_services:
                    additional_equipment.append('儿童座椅')
                if '专人上门送车' in additional_services:
                    additional_equipment.append('上门送车')
                if '专人上门取车' in additional_services:
                    additional_equipment.append('上门取车')
                
                # 检查Insurance中的保险类型
                if 'TPL,CDW(Excess:¥0.00-¥0.00)' in insurance:
                    additional_equipment.append('保险')
                if 'NOC(Excess:¥0.00-¥0.00)' in insurance:
                    additional_equipment.append('NOC')
                
                # 写入订单数据
                ws.cell(row=current_row, column=1, value=item['staff'])
                ws.cell(row=current_row, column=2, value=item['type'])
                ws.cell(row=current_row, column=3, value=item['order_id'])
                # 根据附加设备和订单类型设置订单号单元格的填充颜色
                if item['type'] == '租车':
                    if '雪胎' in additional_equipment:
                        ws.cell(row=current_row, column=3).fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
                    elif '儿童座椅' in additional_equipment:
                        ws.cell(row=current_row, column=3).fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
                ws.cell(row=current_row, column=4, value=item['time'].strftime('%H:%M'))
                ws.cell(row=current_row, column=5, value=order['location'])
                ws.cell(row=current_row, column=6, value=order['rental_days'])
                ws.cell(row=current_row, column=7, value=order['vehicle'])
                ws.cell(row=current_row, column=8, value=order['customer_name'])
                ws.cell(row=current_row, column=9, value=order['customer_phone'])
                ws.cell(row=current_row, column=10, value=order['customer_email'])
                
                # 根据订单类型设置第二个租还时间
                if item['type'] == '租车':
                    dropoff_time = pd.to_datetime(order['Drop-off time (local)'])
                    ws.cell(row=current_row, column=11, value=dropoff_time.strftime('%m/%d %H:%M'))
                else:
                    pickup_time = pd.to_datetime(order['Pick-up time (local)'])
                    ws.cell(row=current_row, column=11, value=pickup_time.strftime('%m/%d %H:%M'))
                ws.cell(row=current_row, column=12, value=order['Drop-off location'] if item['type'] == '租车' else order['Pick-up location'])
                
                ws.cell(row=current_row, column=13, value=', '.join(additional_equipment))
                current_row += 1
            
            # 在不同地址组之间添加空行
            current_row += 1
        
        # 添加车型统计
        current_row += 1
        ws.cell(row=current_row, column=1, value='车型统计').font = Font(bold=True)
        current_row += 1
        
        # 统计每种车型的数量
        vehicle_counts = {}
        for item, order_data in all_orders:
            # 只统计租车订单的车型
            if item['type'] == '租车':
                vehicle = order_data['vehicle']
                if vehicle not in vehicle_counts:
                    vehicle_counts[vehicle] = 0
                vehicle_counts[vehicle] += 1
        
        # 写入车型统计数据
        for vehicle, count in vehicle_counts.items():
            ws.cell(row=current_row, column=1, value=vehicle)
            ws.cell(row=current_row, column=2, value=count)
            current_row += 1
        
        # 设置边框样式
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 为所有单元格添加边框
        for row in ws.iter_rows(min_row=1, max_row=current_row-1, min_col=1, max_col=13):
            for cell in row:
                cell.border = thin_border
        
        # 调整列宽
        for column in ws.columns:
            max_length = 0
            column = [cell for cell in column if not isinstance(cell, openpyxl.cell.cell.MergedCell)]
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = (max_length + 2)
            if column and not isinstance(column[0], openpyxl.cell.cell.MergedCell):
                ws.column_dimensions[column[0].column_letter].width = adjusted_width
        
        # 保存文件
        wb.save(filename)

def main():
    from datetime import datetime

    try:
        scheduler = AutoScheduler()
        pickup_file, dropoff_file = scheduler.find_order_files()

        if not pickup_file or not dropoff_file:
            print("错误：在下载文件夹中未找到明天的租车和还车订单文件")
            sys.exit(1)

        print(f"找到租车订单文件：{pickup_file}")
        print(f"找到还车订单文件：{dropoff_file}")

        scheduler.load_orders(pickup_file, dropoff_file)
        
        # 生成明天的排班表
        tomorrow = datetime.now().date() + timedelta(days=1)
        scheduler.generate_schedule(tomorrow, 1)
        
        # 导出到Excel文件
        output_filename = f"排班表_{tomorrow.strftime('%Y%m%d')}.xlsx"
        scheduler.export_to_excel(output_filename)
        print(f"排班表已生成：{output_filename}")

    except Exception as e:
        print(f"错误：{str(e)}")
        sys.exit(1)

if __name__ == '__main__':
    main()