钉钉考勤报表加班时间统计

最新推荐文章于 2025-04-29 15:30:59 发布
heima9000
最新推荐文章于 2025-04-29 15:30:59 发布
阅读量681
点赞数 2
文章标签：钉钉 linux 运维
本文链接：https://blog.csdn.net/heima9000/article/details/140612705
版权
最近公司裁员，一时兴起，写了个钉钉考勤报表加班时间统计的小工具，方便打工人被裁员时快速统计自己的加班时间。这个小工具可以填写多个连续的合同日期，同时显示两中加班工资计算方式：一种是按照配置的加班属于有效加班时间计算的加班费，另一种是按照实际下班时间作为有效的加班时间计算的加班费。
import sys
import openpyxl
from datetime import datetime
import re
import os
import time
from PyQt5.QtWidgets import QApplication, QWidget, QVBoxLayout, QHBoxLayout, QLabel, QLineEdit, QPushButton, QTextEdit, QFileDialog, QApplication

class ContractCalculator(QWidget):
    def __init__(self):
        super().__init__()
        self.setWindowTitle('加班费计算器')
        # 创建按钮
        self.folder = './考勤报表'
        self.pickFolderBtn = QPushButton('选择考勤报表文件夹路径', self)
        self.pickFolderBtn.clicked.connect(self.openFolderPicker)

        self.workDate1_sum = [] #存储表格原始日期
        self.workDate2_sum = [] #存储日期
        self.workTime_sum = [] #存储打卡信息
        self.overtimeLen_sum = [] #存储每日对应的加班时长
        self.wage_sum = [] #存储每日对应的月工资
        self.workday_overtimeLenSum = {} #工作日加班时长
        self.overtimeLenSum_6_8 = {} #周日加班时长
        self.allMoney = 0
        self.overtime_start = ""
        self.overtime_end = ""
        self.contract_inputs = []
        self.contract_info = []
        self.allMoney = 0

        for i in range(5):
            start_date = QLineEdit()
            end_date = QLineEdit()
            salary = QLineEdit()
            if i == 0:
                start_date.setText("2022-07-01")
                end_date.setText("2024-06-30")
                salary.setText("15000")
                
            self.contract_inputs.append((start_date, end_date, salary))

        self.overtime_start = QLineEdit("18:00")
        self.overtime_end = QLineEdit("20:00")

        layout = QVBoxLayout()

        # 输入框
        for i in range(5):
            hbox = QHBoxLayout()
            hbox.addWidget(QLabel(f"合同{i+1}开始日期"))
            hbox.addWidget(self.contract_inputs[i][0])
            hbox.addWidget(QLabel("结束日期"))
            hbox.addWidget(self.contract_inputs[i][1])
            hbox.addWidget(QLabel("工资"))
            hbox.addWidget(self.contract_inputs[i][2])
            layout.addLayout(hbox)
        
        overtime_hbox = QHBoxLayout()
        overtime_hbox.addWidget(QLabel("加班开始时间"))
        overtime_hbox.addWidget(self.overtime_start)
        overtime_hbox.addWidget(QLabel("加班结束时间"))
        overtime_hbox.addWidget(self.overtime_end)
        layout.addLayout(overtime_hbox)

        # 显示框
        self.result_text = QTextEdit()
        layout.addWidget(self.result_text)

        # 提示框
        note_text = QTextEdit()
        note_text.setPlainText("周末正常加班按照已经进行了调休不计入加班时长，仅计算所有上班时间晚上的加班时长")
        layout.addWidget(note_text)

        # 计算按钮
        calculate_button = QPushButton("计算")
        calculate_button.clicked.connect(self.calculate_salary)
        layout.addWidget(self.pickFolderBtn)
        layout.addWidget(calculate_button)
        self.setLayout(layout)

    def openFolderPicker(self):
        self.folder = QFileDialog.getExistingDirectory(self, "选择文件夹", "/")  # 获取用户选择的文件夹
        print("选择的文件夹:", self.folder)  # 输出选择的文件夹路径

    def calculate_salary(self):
        result = ""
        self.contract_info = []
        for start_date, end_date, salary in self.contract_inputs:
            self.contract_info.append((start_date.text(), end_date.text(), salary.text()))
            result = start_date.text() + end_date.text() + salary.text()
        
        overtime_start = self.overtime_start.text()
        overtime_end = self.overtime_end.text()

        # 编写工资计算逻辑
        self.readXlsx(self.folder)
        self.writeResult()

        self.result_text.append("生成 加班明细.xlsx")
        

    def getWageByDate(self, date = "2017-01-01"):
        check_datetime = datetime.strptime(date,'%Y-%m-%d')
        for ele in self.contract_info:
            #print(ele)
            if ele[0] == '' or ele[1] == '':
                continue
            start_datetime = datetime.strptime(ele[0],'%Y-%m-%d')
            end_datetime = datetime.strptime(ele[1],'%Y-%m-%d')
            wage = ele[2]
            start_timestamp = datetime.timestamp(start_datetime)
            end_timestamp = datetime.timestamp(end_datetime)
            check_timestamp = datetime.timestamp(check_datetime)
            # print(start_datetime, check_datetime, end_datetime, wage)
            # print(start_timestamp, check_timestamp, end_timestamp, wage)
            if start_timestamp <= check_timestamp <= end_timestamp:
                #print(f"{date} 对应的月工资是{wage}")
                return wage
        print(f"{date}不在合同日期内")
        return 0
    
    def readXlsx(self, filepath= './考勤报表'):
        files_and_folders = os.listdir(filepath)
        xlsx_files = [file for file in files_and_folders if file.endswith(".xlsx")]
        for file in xlsx_files:
            self.result_text.append(f"正在解析 {file}")
            QApplication.processEvents() 
            workDate1 = []
            workDate2 = []
            workTime = []
            overtimeLen = []
            if "打卡时间表" not in file:
                continue
            # 打开工作簿  
            workbook = openpyxl.load_workbook(filepath+"/"+file)  
            
            # 选择第一个工作表（默认通常是第一个）  
            sheet = workbook.active  
            # 获取第1行（注意：索引从1开始，不是从0开始）  
            row_data = [cell.value for cell in sheet[1]]  
            month = row_data[0].split("：")[1].strip().split(" ")[0][:-2]
            # 获取第2行（注意：索引从1开始，不是从0开始）  
            row_data = [cell.value for cell in sheet[2]]   
            # 获取第3行（注意：索引从1开始，不是从0开始）  
            row_data = [cell.value for cell in sheet[3]]  
            dayNum = 0
            for ele in row_data[6:]:
                dayNum = dayNum + 1
                datestr1 = month+ele
                datestr2 = month+"{:02d}".format(dayNum) #获取日期YYYY:MM:DD
                workDate1.append(datestr1)
                self.workDate1_sum.append(datestr1)
                #print(datestr1)
                wage = self.getWageByDate(datestr2)
                self.wage_sum.append(wage)
                if str(wage) not in self.workday_overtimeLenSum:
                    self.workday_overtimeLenSum[str(wage)] = 0
                if str(wage) not in self.overtimeLenSum_6_8:
                    self.overtimeLenSum_6_8[str(wage)] = 0
                workDate2.append(datestr2)
                self.workDate2_sum.append(datestr2)

            # 获取第4行（注意：索引从1开始，不是从0开始）  
            row_data = [cell.value for cell in sheet[4]]   
            #print("len(row_data) = ", len(row_data), row_data)
            dayNum = 0
            for ele in row_data[6:]:
                # print(f"----------------------dayNum = {dayNum}------------------------------------")
                # print(ele)
                if ele is not None:
                    #print(workDate1[dayNum], workDate2[dayNum] )
                    timePeriod=ele.replace("  \n", "~").strip()
                    matches = re.findall(r'\d{2}:\d{2}', timePeriod)
                    # print(timePeriod)
                    # print(matches)
                    last_time = matches[-1]
                    workStartTime = matches[0]
                    workEndTime = last_time
                    workTime.append(timePeriod)
                    self.workTime_sum.append(timePeriod)
                    # 要判断的日期
                    # 将输入日期转换为 datetime 对象
                    date_obj = datetime.strptime(workDate2[dayNum], '%Y-%m-%d')
                    
                    # 使用weekday()方法来获得日期是星期几（星期一为0，星期天为6）
                    day_of_week = date_obj.weekday()

                    time_format = '%H:%M'
                    time1 = datetime.strptime(workEndTime, time_format)
                    time2 = datetime.strptime(self.overtime_start.text(), time_format)
                    time3 = datetime.strptime(self.overtime_end.text(), time_format)
                    if time1 > time2:
                        time_difference = time1 - time2
                        time_difference = time_difference.total_seconds() // 60
                        overtimeLen.append(time_difference)
                        self.overtimeLen_sum.append(time_difference)
                        # print(f"{date_obj}, 星期{day_of_week}, 下班时间{workEndTime}, 加班起始时间{self.overtime_start},加班时长{time_difference}")
                        # print(f"workday_overtimeLenSum[{str(self.getWageByDate(workDate2[dayNum]))}] = {self.workday_overtimeLenSum[str(self.getWageByDate(workDate2[dayNum]))]}")
                        self.workday_overtimeLenSum[str(self.getWageByDate(workDate2[dayNum]))] = self.workday_overtimeLenSum[str(self.getWageByDate(workDate2[dayNum]))] + time_difference
                        # print(f"workday_overtimeLenSum[{str(self.getWageByDate(workDate2[dayNum]))}] = {self.workday_overtimeLenSum[str(self.getWageByDate(workDate2[dayNum]))]}")
                        if time1 > time3:
                            time_difference = time3 - time2
                            time_difference = time_difference.total_seconds() // 60
                            self.overtimeLenSum_6_8[str(self.getWageByDate(workDate2[dayNum]))] = self.overtimeLenSum_6_8[str(self.getWageByDate(workDate2[dayNum]))] + time_difference
                    else:
                        overtimeLen.append(0)
                        self.overtimeLen_sum.append(0)

                else:
                    workTime.append(ele)
                    self.workTime_sum.append(ele)
                    overtimeLen.append(0)
                    self.overtimeLen_sum.append(0)
                dayNum = dayNum + 1
        self.allMoney = 0
        for key,value in self.workday_overtimeLenSum.items():
            if key == '0':
                continue
            money = (int(key)/(21.75*8))*(value/60)*1.5
            self.allMoney = self.allMoney + money
            self.result_text.append(f"工资{key} 按实际下班时间为准，加班时长为{value}分钟 -->{value/60}小时")
            print(f"工资{key} 按实际下班时间为准，加班时长为{value}分钟 -->{value/60}小时")
        print(f"按实际下班时间为准，加班费总计:{self.allMoney}")
        self.result_text.append(f"按实际下班时间为准，加班费总计:{self.allMoney}")
        self.allMoney = 0
        for key,value in self.overtimeLenSum_6_8.items():
            if key == '0':
                continue
            money = (int(key)/(21.75*8))*(value/60)*1.5
            self.allMoney = self.allMoney + money
            self.result_text.append(f"工资{key} 晚上6点起加班时长为{value}分钟 -->{value/60}小时")
            print(f"工资{key} 按晚上8点加班结束，加班时长为{value}分钟 -->{value/60}小时")

        print(f"按加班时间到8点结束为准，加班费总计:{self.allMoney}")
        self.result_text.append(f"按加班时间到8点结束为准，加班费总计:{self.allMoney}")

    def writeResult(self, file = "./加班明细.xlsx"):
        # 创建一个Workbook对象
        wb = openpyxl.Workbook()
        # 选择要写入数据的工作表
        ws = wb.active
        # 写入数据
        title = ["日期","日期","打卡时间","加班时长","月工资"]
        ws.append(title)
        data = [self.workDate1_sum, self.workDate2_sum, self.workTime_sum, self.overtimeLen_sum, self.wage_sum]
        for row in zip(*data):
            ws.append(row)
        # ''' 
        #     计算加班工资
        #     1、工作日加班：小时加班工资标准=劳动合同规定的月工资标准÷(月计薪天数21.75×8小时)×150%;
        #     2、休息日加班：小时加班工资标准=劳动合同规定的月工资标准÷(月计薪天数21.75×8小时)×200%;
        #     3、法定休假日：小时加班工资标准=劳动合同规定的月工资标准÷(月计薪天数21.75×8小时)×300%。
        # '''
        for key,value in self.workday_overtimeLenSum.items():
            if key == '0':
                continue
            money = (int(key)/(21.75*8))*(value/60)*1.5
            row = [f"工资{key} 按实际下班时间为准，加班时长为{value}分钟 -->{value/60}小时-->加班费{money}"]
            ws.append(row)

        row = [f"按实际下班时间为准，加班费总计:{self.allMoney}"]
        ws.append(row)

        self.allMoney = 0
        for key,value in self.overtimeLenSum_6_8.items():
            if key == '0':
                continue
            money = (int(key)/(21.75*8))*(value/60)*1.5
            self.allMoney = self.allMoney + money
            row = [f"工资{key} 按晚上8点加班结束，加班时长为{value}分钟 -->{value/60}小时-->加班费{money}"]
            ws.append(row)
        row = [f"按加班时间到8点结束为准，加班费总计:{self.allMoney}"]
        ws.append(row)
        # 保存Excel文件
        wb.save(file)

if __name__ == '__main__':
    app = QApplication(sys.argv)
    calculator = ContractCalculator()
    calculator.show()
    sys.exit(app.exec_())