"""
 @Author: jian
 @Email: ajian100813@126.com
 @FileName: convert.py
 @DateTime: 2025/1/16 21:25
 @Project: convert-excel
 @Version: 1.0
"""
import numpy as np
import pandas as pd
from datetime import datetime, time, timedelta

# 定义时间格式常量
TIME_FORMAT = "%H:%M:%S"
# 定义常量
MORNING_START = datetime.strptime("09:00:00", TIME_FORMAT).time()
MORNING_END = datetime.strptime("12:30:00", TIME_FORMAT).time()
AFTERNOON_START = datetime.strptime("14:00:00", TIME_FORMAT).time()
AFTERNOON_END = datetime.strptime("18:30:00", TIME_FORMAT).time()

# 计算工作有效时间
def calculate_work_time(work_start_time: time, work_end_time: time):
    # 总的工作时长等于上午加下午，调用两个函数实现
    morning_work_time = calculate_morning_work_time(work_start_time, work_end_time)
    afternoon_work_time = calculate_afternoon_work_time(work_start_time, work_end_time)

    # 返回 工作有效时间 ，类型为timedelta
    total_work_time = morning_work_time + afternoon_work_time
    return total_work_time

# 计算上午有效时间
def calculate_morning_work_time(work_start_time: time, work_end_time: time):
    # 将time对象转换为datetime对象，使用默认日期
    default_date = datetime.strptime("1900-01-01", "%Y-%m-%d")
    start_datetime = default_date.replace(hour=work_start_time.hour, minute=work_start_time.minute, second=work_start_time.second)
    end_datetime = default_date.replace(hour=work_end_time.hour, minute=work_end_time.minute, second=work_end_time.second)

    # 上午开始和结束时间
    morning_start_datetime = default_date.replace(hour=MORNING_START.hour, minute=MORNING_START.minute, second=MORNING_START.second)
    morning_end_datetime = default_date.replace(hour=MORNING_END.hour, minute=MORNING_END.minute, second=MORNING_END.second)

    # 计算上午有效时间
    if start_datetime < morning_start_datetime:
        start_datetime = morning_start_datetime
    if end_datetime > morning_end_datetime:
        end_datetime = morning_end_datetime

    if start_datetime < end_datetime:
        return end_datetime - start_datetime
    else:
        return timedelta(0)

# 计算下午有效时间
def calculate_afternoon_work_time(work_start_time: time, work_end_time: time):
    # 将time对象转换为datetime对象，使用默认日期
    default_date = datetime.strptime("1900-01-01", "%Y-%m-%d")
    start_datetime = default_date.replace(hour=work_start_time.hour, minute=work_start_time.minute, second=work_start_time.second)
    end_datetime = default_date.replace(hour=work_end_time.hour, minute=work_end_time.minute, second=work_end_time.second)

    # 下午开始和结束时间
    afternoon_start_datetime = default_date.replace(hour=AFTERNOON_START.hour, minute=AFTERNOON_START.minute, second=AFTERNOON_START.second)
    afternoon_end_datetime = default_date.replace(hour=AFTERNOON_END.hour, minute=AFTERNOON_END.minute, second=AFTERNOON_END.second)

    # 计算下午有效时间
    if start_datetime < afternoon_start_datetime:
        start_datetime = afternoon_start_datetime
    if end_datetime > afternoon_end_datetime:
        end_datetime = afternoon_end_datetime

    if start_datetime < end_datetime:
        return end_datetime - start_datetime
    else:
        return timedelta(0)


# 假设 df['WorkHours'] 是 timedelta 类型
def timedelta_to_hms(td):
    if pd.isna(td):
        return np.nan
    total_seconds = int(td.total_seconds())
    hours = total_seconds // 3600
    minutes = (total_seconds % 3600) // 60
    seconds = total_seconds % 60
    return f"{hours:02}:{minutes:02}:{seconds:02}"

def main():
    # 示例时间字符串
    start_time_str = "14:30:00"
    end_time_str = "18:45:00"

    # 将时间字符串解析为time对象
    start_time = datetime.strptime(start_time_str, TIME_FORMAT).time()
    end_time = datetime.strptime(end_time_str, TIME_FORMAT).time()

    # 计算工作有效时间
    work_time = calculate_work_time(start_time, end_time)

    # 打印结果
    print(f"工作有效时间: {work_time}")



    # 读取Excel文件
    df = pd.read_excel('your_file.xlsx')

    # 假设时间列名为 'StartTime' 和 'EndTime'
    start_time_column = 'StartTime'
    end_time_column = 'EndTime'

    # 将时间列转换为time对象
    df[start_time_column] = pd.to_datetime(df[start_time_column], format='%H:%M:%S').dt.time
    df[end_time_column] = pd.to_datetime(df[end_time_column], format='%H:%M:%S').dt.time

    # 计算工作有效时间
    df['WorkHours'] = df.apply(lambda row: calculate_work_time(row[start_time_column], row[end_time_column]), axis=1)

    # 在 excel表中的 WorkHours 列中自动填入数据
    # 注意字符串的格式为 %H:%M:%S

    # 应用转换函数
    df['WorkHours'] = df['WorkHours'].apply(timedelta_to_hms)
    df.to_excel('output.xlsx', index=False)


    # 打印结果
    print(df.head())
    print(df.dtypes)

if __name__ == '__main__':
    while True:
        main()
        user_input = input("请输入 'exit' 退出程序, 'restart' 重启程序: ")
        if user_input.lower() == 'exit':
            print("程序退出。")
            break
        elif user_input.lower() == 'restart':
            print("程序重启。")