import os
import xlrd
import shutil
import pandas as pd
import datetime
import json
import re
entryTimeThreshold = "8.30" # 迟到的时间
exitTimeThreshold = "11.30" # 早退的时间
numberOfEntries = "3" # 频繁进出的阈值
sumAttendanceTime = "3:30" # 累计观看时长
print("designed by ksyzz zhinengxi")
print("if you find bug or others please send email to zhangda131236@163.com")
print("-"*20)
print("注意:该程序所在的文件夹有一个 迟到时间等信息的设置.txt文件\r\n"
"如果您还没有设置,请先关闭程序请打开文件设置迟到时间,早退时间等信息,然后再打开程序\r\n")
configPath = r"C:\Users\admin\Desktop\逃课威龙\迟到时间等信息的设置.txt"
configPath = os.path.join(os.path.abspath('.'), "迟到时间等信息的设置.txt")
with open(configPath, 'r')as f: # 打开txt文件
s = f.read()
s = re.sub(u"\\(.*?\\)|\\{.*?}|\\[.*?]", "", s)
s = s.replace("\n", "").replace("\r", "").replace(" ", "")
s = json.loads(s)
entryTimeThreshold = s['lateTime']
exitTimeThreshold = s['earlyLeaveTime']
numberOfEntries = s['frequencyThreshold']
sumAttendanceTime = s['sumTimeThreshold']
studentRosterFilePath = r"C:\Users\admin\Desktop\逃课威龙\21中专机电1班名单.xlsx"
# studentRosterFilePath = input(r"请输入班级名单的路径如C:\Users\admin\Desktop\21中专机电1班名单.xlsx"+"\r\n"
# r"该文件必须有一列叫姓名, 姓名这一列中保存了所有要签到的学生的名字"+"\r\n"
# r"粘贴完或者输完后按enter键即可 [右击文件 选择属性 点击安全 就可以看到路径 复制粘贴来即可]"+"\r\n"
# r"复制后粘贴的方法是按一下鼠标右键"+"\r\n"
# r"请输入班级名单地址:")
studentRosterDF = pd.read_excel(studentRosterFilePath)
attendanceFormFilePath = r"C:\Users\admin\Desktop\逃课威龙\a.xlsx"
# attendanceFormFilePath = input(r"请输入从腾讯会议导出的原始数据"+"\r\n"
# r"粘贴完输完后按enter键即可 [右击文件 选择属性 点击安全 就可以看到路径 复制粘贴来即可]"+"\r\n"
# r"请输入腾讯会议导出的信息地址:")
attendanceFormDF = pd.read_excel(attendanceFormFilePath)
print(attendanceFormDF["首次入会时间"])
wholeStudentName = list(studentRosterDF["姓名"])
absenseStudent = list(studentRosterDF["姓名"]) # 到一个删除一个 剩下的就是没到的
PatrolAndTeacher = [] # 巡课教师
# 总目标:
# 生成键值对 键为用户昵称 值为 [{首次入会时间:xxxx}, {最后退会时间:xxxx}, {入会次数} {参会时间}]
# 步骤1: 将昵称统一修改成姓名 筛选出没到的学生 和 巡课的教师
flag_sucessMach = 0 # 学生姓名替换标志位(不太优雅 可改进)
for attendmettingNickName in attendanceFormDF["用户昵称(入会昵称)"].values:
for studentName in wholeStudentName: # 遍历所有与学生的姓名
if studentName in attendmettingNickName:
attendanceFormDF["用户昵称(入会昵称)"].replace(attendmettingNickName, studentName, inplace=True)
absenseStudent.remove(studentName)
flag_sucessMach = 1
break # 成功匹配了就提前结束
if flag_sucessMach == 0:
PatrolAndTeacher.append(attendmettingNickName)
flag_sucessMach = 0
print(absenseStudent)
exit()
attendanceDict = attendanceFormDF.set_index("用户昵称(入会昵称)").agg(list, axis=1).to_dict()
# 生成键值对 如: {"韩梅梅":{"首次进入时间":"xxxx", "退会时间":"xxxxx"},"入会次数":2, 累计参会时长:"xxx" }
attendanceStudentDict = {}
for name, deatilInformation in attendanceDict.items():
if name not in PatrolAndTeacher: # 巡课教师不加入
attendanceStudentDict[name] = {"首次进入时间": deatilInformation[0].to_pydatetime(),
"退会时间": deatilInformation[1],
"入会次数": deatilInformation[2],
"累计参会时长": deatilInformation[3],
}
# 检查学生(来了的)
# 首次进入时间晚于8.30算迟到 此处应该可自由设置
# 退出时间早于11.30算早退 此处应该可自由设置
# 进出次数大于3次算有问题 此处应该可自由设置
# 累计参会时长小于3.10分钟的算有问题 此处应该可自由设置
lateStudentList = [] # 迟到的学生
leaveEarlyStudentList = [] # 早退的学生
exceedFrequency = [] # 次数频繁的学生
lowerThanSumAttendanceTime = [] # 低于总时长的学生
entryTimeThreshold = datetime.datetime.strptime(entryTimeThreshold, '%H.%M')
exitTimeThreshold = datetime.datetime.strptime(exitTimeThreshold, '%H.%M')
sumAttendanceTime = datetime.datetime.strptime(sumAttendanceTime, '%H:%M')
numberOfEntries = int(numberOfEntries)
for key, deatilInformationdict in attendanceStudentDict.items():
if entryTimeThreshold.time() < deatilInformationdict["首次进入时间"].time():
lateStudentList.append({key: str(deatilInformationdict["首次进入时间"].time())})
if isinstance(deatilInformationdict["退会时间"], str): # 退会时间可能是-(下载的时候还没有退会)
pass
elif exitTimeThreshold.time() > deatilInformationdict["退会时间"].time():
leaveEarlyStudentList.append({key: deatilInformationdict["退会时间"].time().strftime("%H:%M")})
if int(deatilInformationdict["入会次数"]) > numberOfEntries:
exceedFrequency.append({key: deatilInformationdict["入会次数"]})
if deatilInformationdict["累计参会时长"] < sumAttendanceTime.time():
lowerThanSumAttendanceTime.append({key: deatilInformationdict["累计参会时长"].strftime("%H:%M")})
print("迟到的学生:", lateStudentList)
print("早退的学生:", leaveEarlyStudentList)
print("频繁出入的学生:", exceedFrequency)
print("总时长不够的学生:", lowerThanSumAttendanceTime)
print("缺席学生:", len(absenseStudent), absenseStudent)
print("任课和巡课教师: ", len(PatrolAndTeacher), PatrolAndTeacher)
fileSavePath = os.path.join(os.path.abspath('.'), datetime.datetime.now().strftime("%m%d%H%M") + "迟到早退等学生名单统计.txt")
with open(fileSavePath, 'w') as file_object:
file_object.write("---------------缺席的学生---------------\n")
for name in absenseStudent:
file_object.write(name+"\n")
file_object.write("---------------迟到的学生---------------\n")
for lateStudentNameDic in lateStudentList:
for key, value in lateStudentNameDic.items():
file_object.write(key+" "+value)
file_object.write("\n")
file_object.write("---------------早退的学生---------------\n")
for leaveEarlyStudentDic in leaveEarlyStudentList:
for key, value in leaveEarlyStudentDic.items():
file_object.write(key+" "+value)
file_object.write("\n")
file_object.write("---------------频繁退出的学生---------------\n")
for exceedFrequencyStudentDic in exceedFrequency:
for key, value in exceedFrequencyStudentDic.items():
file_object.write(key+" "+str(value))
file_object.write("\n")
file_object.write("---------------在线总时长不足的学生---------------\n")
for lowerThanSumAttendanceTimeStudentDic in lowerThanSumAttendanceTime:
for key, value in lowerThanSumAttendanceTimeStudentDic.items():
file_object.write(key+" "+value)
file_object.write("\n")
file_object.write("---------------任课和巡课教师---------------\n")
for name in PatrolAndTeacher:
file_object.write(name)
file_object.write("\n")
print(fileSavePath)
print("已完成,考勤信息在当前程序所在的目录下, 按任意键退出....")
08-23
81
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)