工时计算程序

工时计算

# coding=utf-8
import numpy as np
import pandas as pd
import datetime
import collections


# 计算时间差函数
def difftime(click_in, click_out):
    # 输入的日期和时间是字符串形式，需要先将字符串格式化为datetime形式。
    time1 = datetime.datetime.strptime(click_in, "%H:%M")
    time2 = datetime.datetime.strptime(click_out, "%H:%M")
    num = (time2-time1).seconds/60
    return num


# 得到每一个姓名下所有数据的行所引
def checkId(target, a):
    b = []
    for index, nums in enumerate(a):
        if nums == target:
            b.append(index)
    return (b)


in_path = "20210501-20210531_1(无单多次打卡数据).xlsx"
df = pd.read_excel(in_path, skiprows=0, header=0)
print(df)
# 提取工时计算所需数据
Name = df["姓名"].astype("str")
Company = df["部门"].astype("str")
Date_attendance = df["考勤日期"].astype("str")
Clock_time = df["打卡时间"].astype("str")
Group_attendance = df["考勤组"].astype("str")
Clock_result = df["打卡结果"].astype("str")

"""
# 计算该公司内每个员工的，0.工作日正常小时、1.工作日加班小时、2.双休日加班小时、3.法定节假日加班小时
"""
Every_Name_company = [item for item, count in collections.Counter(Name).items() if count >= 1]
# 遍历每个员工，利用相应数据计算出四个工时
work_time_total_list_all = []
overtime_workday_total_list_all = []
weekend_overtime_workday_list_all = []
holiday_overtime_workday_list = []
Company_Every_person_list = []
department_Every_person_list = []
workday_numbers_list = []
overtime_workday_days_list = []
weekend_overtime_days_list = []
holiday_overtime_workday_list_all = []
# global weekend_overtime_days
for i in Every_Name_company:

    print(i)
    # 这个人考勤日期、打卡时间的索引
    Date_index_Every_person = checkId(i, Name)
    # 取出每个人的考勤日期、打卡时间数据
    Date_attendance_Every_person = Date_attendance[Date_index_Every_person]
    Clock_time_Every_person = Clock_time[Date_index_Every_person]
    # 数据划分。三类数据，周一-周五：工作日，周六、周日：双休日，法定节假日（待实现，周主任要公司年历，调休规则）,以考勤日期进行筛选。
    weekend_index = []
    weekend_index_all = []
    work_index = []
    work_index_special = []
    work_index_all = []
    holiday_index = []
    holiday_index_all = []

    for D in Date_attendance_Every_person:
        holiday_str_list = ["21-05-01"]
        if any(key in str(D) for key in holiday_str_list):
            holiday_index = Date_attendance_Every_person[(Date_attendance_Every_person == str(D))].index.tolist()
        # 2月份调休规则：21-02-06和21-02-07为工作日
        elif "星期六" in str(D) or "星期日" in str(D) or "21-05-03" in str(D) or "21-05-04" in str(D) or "21-05-05" in str(D):

            if "21-05-08" not in str(D):
                 weekend_index = Date_attendance_Every_person[(Date_attendance_Every_person == str(D))].index.tolist()
            else:
                work_index_special = Date_attendance_Every_person[(Date_attendance_Every_person == str(D))].index.tolist()

        else:
            work_index = Date_attendance_Every_person[(Date_attendance_Every_person == str(D))].index.tolist()

        # 工作日索引
        work_index_all = work_index_all + work_index + work_index_special
        # 周六日索引
        weekend_index_all = weekend_index_all + weekend_index
        # 节假日索引
        holiday_index_all = holiday_index_all + holiday_index

    # 剔除正常工作日、双休日和节假日重复索引
    work_index_all = sorted(list(set(work_index_all)))
    weekend_index_all = sorted(list(set(weekend_index_all)))
    holiday_index_all = sorted(list(set(holiday_index_all)))
    print("工作日索引", work_index_all)
    print("双休日索引", weekend_index_all)
    print("法定假日索引", holiday_index_all)

    # 计算工时
    """
    # 这个人工作日每天正常小时计算，周一至周五
    """
    # 工作日正常工作
    work_time_std_1 = []
    work_time_std_2 = []
    work_time_early = []
    work_time_late = []
    late_early_time = []
    overtime_workday = []
    # 工作日工时
    Clock_time_Every_person_work = Clock_time[work_index_all]
    # 每次取出连续两行数据，每次拿出每个人一天的打卡时间
    workday_numbers = 0
    overtime_workday_days = 0
    for j in [Clock_time_Every_person_work[k: k + 2] for k in range(0, len(Clock_time_Every_person_work), 2)]:
        clock_in_time = str(j.iloc[0])
        clock_out_time = str(j.iloc[-1])
        # 字符串切片，为了比较，只要时间字符串，不要日期字符
        clock_in_time = clock_in_time[11: 16]
        clock_out_time = clock_out_time[11: 16]

        # 工作日正常小时计算
        clock_in_std_time = "08:30"
        clock_out_std_time = "18:00"

        # 如果上班打卡早于8：30，下班打卡晚于18：00，这一天工作日正常小时为8小时
        if clock_in_time.__gt__(clock_in_std_time) is False and clock_out_time.__gt__(clock_out_std_time) is True:
            work_time_std_1 = work_time_std_1 + [8 * 60]
        # 另外一种正常情况，如果上班早于9点，下班早于18点，如果下班早于18点，弹性工作
        elif clock_in_time.__gt__(clock_in_std_time) is False and clock_out_time.__gt__(clock_out_std_time) is False:
            if (difftime(clock_in_time, clock_in_std_time) - difftime(clock_out_time, clock_out_std_time)) > 0:
                work_time_std_2 = work_time_std_2 + [8 * 60]

            else:
                early_time = difftime(clock_out_time, clock_out_std_time) - difftime(clock_in_time, clock_in_std_time)
                # 新的算法
                early_std_time = np.linspace(0.5, 8, 16)
                for k in early_std_time:
                    if early_time < k * 60 and early_time > (k - 0.5) * 60:
                        work_time_early = work_time_early + [8 * 60 - k * 60]

        # 迟到情况工时计算
        elif clock_in_time.__gt__(clock_in_std_time) is True and clock_out_time.__gt__(clock_out_std_time) is True:
            # 迟到时间
            late_time = difftime(clock_in_std_time, clock_in_time)
            late_std_time = np.linspace(0.5, 8, 16)
            for k in late_std_time:
                if late_time <= k * 60 and late_time > (k - 0.5) * 60:
                    work_time_late = work_time_late + [8 * 60 - k * 60]

        else:
            late_time_1 = difftime(clock_in_std_time, clock_in_time)
            earlt_time_1 = difftime(clock_out_time, clock_out_std_time)
            late_early_time_1 = late_time_1 + earlt_time_1
            late_early_1_std_time = np.linspace(0.5, 8, 16)
            for k in late_early_1_std_time:
                if late_early_time_1 <= k * 60 and late_early_time_1 > (k - 0.5) * 60:
                    late_early_time = late_early_time + [8 * 60 - k * 60]

        # 计算工作日加班时间
        if clock_out_time.__gt__(clock_out_std_time) is True:
            if difftime(clock_out_std_time, clock_out_time) >= 60:
                overtime_workday = overtime_workday + [difftime(clock_out_std_time, clock_out_time) - 30]
                overtime_workday_days = overtime_workday_days + 1
        # 计算这个人的工作日天数
        workday_numbers = workday_numbers + 1

    # 计算双休日加班时间
    weekend_overtime_workday_list = []
    if len(weekend_index_all) == 0:
        weekend_overtime_workday_list = weekend_overtime_workday_list + [0]

    else:

        Clock_time_Every_person_overtime_workday = Clock_time[weekend_index_all]
        weekend_overtime_workday = []
        weekend_overtime_days = 0
        for r in [Clock_time_Every_person_overtime_workday[p: p + 2] for p in range(0, len(Clock_time_Every_person_overtime_workday), 2)]:

            weekend_clock_in_time = str(r.iloc[0])
            weekend_clock_out_time = str(r.iloc[-1])
            # 字符串切片，为了比较，只要时间字符串，不要日期字符
            weekend_clock_in_time = weekend_clock_in_time[11: 16]
            weekend_clock_out_time = weekend_clock_out_time[11: 16]
            print(weekend_clock_in_time)
            print(weekend_clock_out_time)
            if difftime(weekend_clock_in_time, weekend_clock_out_time) < 90:
                weekend_overtime_workday_list = weekend_overtime_workday_list + [0]
            else:
                # 减90是中午吃饭时间
                weekend_overtime_workday_list = weekend_overtime_workday_list + [difftime(weekend_clock_in_time,
                                                                                          weekend_clock_out_time) - 90]
                weekend_overtime_days = weekend_overtime_days + 1

    # 计算法定节假日加班时间,与周六日加班时间如出一辙
    holiday_overtime_workday_list = []
    if len(holiday_index_all) == 0:
        holiday_overtime_workday_list = holiday_overtime_workday_list + [0]

    else:

        Clock_time_Every_person_overtime_holiday = Clock_time[holiday_index_all]
        holiday_overtime_workday = []
        # holiday_overtime_days = 0
        for r in [Clock_time_Every_person_overtime_holiday[p: p + 2] for p in
                  range(0, len(Clock_time_Every_person_overtime_holiday), 2)]:
            holiday_clock_in_time = str(r.iloc[0])
            holiday_clock_out_time = str(r.iloc[-1])
            # 字符串切片，为了比较，只要时间字符串，不要日期字符
            holiday_clock_in_time = holiday_clock_in_time[11: 16]
            holiday_clock_out_time = holiday_clock_out_time[11: 16]
            if difftime(holiday_clock_in_time, holiday_clock_out_time) < 90:
                holiday_overtime_workday_list = holiday_overtime_workday_list + [0]
            else:
                # 减90是中午吃饭时间
                holiday_overtime_workday_list = holiday_overtime_workday_list + [
                    difftime(holiday_clock_in_time, holiday_clock_out_time) - 90]
                # holiday_overtime_days = holiday_overtime_days + 1

    # 汇 总 生成各种工时统计列表
    work_time_total_list = work_time_std_1 + work_time_std_2 + work_time_early + work_time_late + late_early_time
    # 每个人的工作日正常小时
    work_time_total = round(sum(work_time_total_list) / 60)
    work_time_total_list_all.append(work_time_total)
    print("工作日工时", work_time_total)
    # 每个人工作日加班小时
    overtime_workday_total = round(sum(overtime_workday) / 60)
    overtime_workday_total_list_all.append(overtime_workday_total)
    print("工作日加班小时", overtime_workday_total)
    # 每个人双休日加班小时
    weekend_overtime_workday_list = round(sum(weekend_overtime_workday_list) / 60)
    weekend_overtime_workday_list_all.append(weekend_overtime_workday_list)
    print("双休日加班小时", weekend_overtime_workday_list)
    holiday_overtime_workday_list = round(sum(holiday_overtime_workday_list) / 60)
    holiday_overtime_workday_list_all.append(holiday_overtime_workday_list)
    print("法定节假日加班小时", holiday_overtime_workday_list)
    # 每个人对应公司
    Company_Every_person = list(set(Company[Date_index_Every_person].tolist()))
    Company_Every_person = " ".join(Company_Every_person)
    department_Every_person = list(set(Group_attendance[Date_index_Every_person].tolist()))
    department_Every_person = " ".join(department_Every_person)
    Company_Every_person_list.append(Company_Every_person)
    department_Every_person_list.append(department_Every_person)
    # 天数
    workday_numbers_list.append(workday_numbers)
    overtime_workday_days_list.append(overtime_workday_days)
    # weekend_overtime_days_list.append(weekend_overtime_days_1)

    print(workday_numbers_list)
    print(overtime_workday_days_list)
    print(overtime_workday_days_list)

"""
# 建立输出数据的DataFrame
"""
# 序号
s = len(Every_Name_company)
Number_list = [i for i in range(1, s + 1)]
print(s)
# 公司
Company_Every_person_list_all = Company_Every_person_list
# 姓名
Name_list = Every_Name_company
# 部门
Department_list = department_Every_person_list

# 生成DataFrame
df_3 = {"序号": Number_list, "公司": Company_Every_person_list_all, "姓名": Name_list, "部门": Department_list,
        "0.工作日正常小时": work_time_total_list_all, "1.工作日加班小时": overtime_workday_total_list_all,
        "2.双休日加班小时": weekend_overtime_workday_list_all,
        "3.法定节假日加班小时": holiday_overtime_workday_list_all, "工作日出勤天数": workday_numbers_list}
df_3 = pd.DataFrame(df_3)
print(df_3)
df_3.to_excel("D:\\", index=False)