# & cls & gawk -f %0 %1 & pause & exit
BEGIN{
	FS=",| "
	check[1][1]=600;check[1][2]=815;check[1][3]=1111;check[1][4]=1230
	check[2][1]=1400;check[2][2]=1500;check[2][3]=1610;check[2][4]=1810
	check[3][1]=1830;check[3][2]=1910;check[3][3]=2100;check[3][4]=2200
}
NR>1{
	sub(/:/,"",$8)
	arr[$3][$7]=arr[$3][$7]""$8" "
}
END{
	#统计数据
	asorti(arr,ta)
	for(id in ta){
		name=ta[id]
		for(day in arr[name]){
			split(arr[name][day],arrTime," ")
			for(x in check){
				earlierFlag=0;laterFlag=0
				for(y in arrTime){
					if(arrTime[y]>=check[x][1]&&arrTime[y]<=check[x][2]){earlierFlag=1;earlierTime=arrTime[y]}
					if(arrTime[y]>=check[x][3]&&arrTime[y]<=check[x][4]){laterFlag=1;laterTime=arrTime[y]}
				}
				#分类统计几种情况
				if(earlierFlag && laterFlag){
					sub(earlierTime,"",arr[name][day])
					sub(laterTime,"",arr[name][day])
					arr[name"-"day]=arr[name"-"day]" "earlierTime","laterTime",1,"
					sTmp="";arrTotal[name]++
					if(earlierTime>=1830){sTmp="1,"}else{sTmp=","}
					fday=gensub("/"," ","g",day)
					weekday=strftime("%A",mktime(fday" 0 0 0"))
					if((weekday=="星期二"||weekday=="星期四")&&earlierTime<=730){
						sTmp=sTmp"1,"
					}else{
						sTmp=sTmp","
					}
					if(weekday=="星期一"&&earlierTime<=720){sTmp=sTmp"1,"}else{sTmp=sTmp","}
					arr[name"-"day]=arr[name"-"day]""sTmp
				}
			}
		}
	}
	#打印处理
	print "姓名,打卡日期,星期,上班,下班,考勤段数,晚修段,早读,升旗,个人总段" >"result.csv"
	for(id in ta){
		name=ta[id]
		for(day in arr[name]){
			fday=gensub("/"," ","g",day)
			weekday=strftime("%A",mktime(fday" 0 0 0"))
			split(arr[name][day],arrTime," ")
			for(x in arrTime){
				if(!isFirst[name]++){
					tPrint=name","day","weekday","arrTime[x]",,,,,,"arrTotal[name]
				}else{
					tPrint=","day","weekday","arrTime[x]
				}
				sPrint=sPrint""tPrint"\n"
			}
			if(length(arr[name"-"day])){
				split(arr[name"-"day],arrRight," ")
				for(x in arrRight){
					if(!isFirst[name]++){
						tPrint=name","day","weekday","arrRight[x]""arrTotal[name]
					}else{
						tPrint=","day","weekday","arrRight[x]
					}
					sPrint=sPrint""tPrint"\n"
				}
			}
		}
	}
	#去掉最后一个换行符
	sPrint=substr(sPrint,1,length(sPrint)-1)
	split(sPrint,arrLine,"\n")
	#处理时间格式
	for(x in arrLine){
		split(arrLine[x],arrCol,",");arrLine[x]=""
		len=length(arrCol[4])
		arrCol[4]=substr(arrCol[4],1,len-2)":"substr(arrCol[4],len-1,2)
		if(len=length(arrCol[5])){
			arrCol[5]=substr(arrCol[5],1,len-2)":"substr(arrCol[5],len-1,2)
		}
		for(y in arrCol){arrLine[x]=arrLine[x]""arrCol[y]","}
		lines=lines""arrLine[x]"\n"
	}
	print lines >>"result.csv"
}
复制代码

        2015-11-20        星期五        11:08                
        2015-11-20        星期五        7:10        11:15        1
复制代码

条件1：同一日期  上班：06：00分 - 08：15分、下班：11：11分-12：30分

条件2：同一日期  上班：14：00分 - 15：00分 、下班：16：10分 - 18：10分

条件3：同一日期  晚修：18：30 - 19：10 下班：21：00 - 22：00分
复制代码

	
		' ' '
		strSQL = "Select `打卡时间` From [" & GetFileNameEx(dbSource, "FN") & "] Where `姓名`='" & objRS2.Fields("姓名") & "' And " & _ 
			"`打卡时间`>=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 06:00:00") & "') And " & _
			"`打卡时间`<=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 08:15:00") & "')"
		objRS2.Fields("上班1") = StringToDate(RunSQLReturnString(objConn1, strSQL))
		
		strSQL = "Select `打卡时间` From [" & GetFileNameEx(dbSource, "FN") & "] Where `姓名`='" & objRS2.Fields("姓名") & "' And " & _ 
			"`打卡时间`>=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 11:11:00") & "') And " & _
			"`打卡时间`<=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 12:30:00") & "')"
		objRS2.Fields("下班1") = StringToDate(RunSQLReturnString(objConn1, strSQL))
		
		strSQL = "Select `打卡时间` From [" & GetFileNameEx(dbSource, "FN") & "] Where `姓名`='" & objRS2.Fields("姓名") & "' And " & _ 
			"`打卡时间`>=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 14:00:00") & "') And " & _
			"`打卡时间`<=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 15:00:00") & "')"
		objRS2.Fields("上班2") = StringToDate(RunSQLReturnString(objConn1, strSQL))
		
		strSQL = "Select `打卡时间` From [" & GetFileNameEx(dbSource, "FN") & "] Where `姓名`='" & objRS2.Fields("姓名") & "' And " & _ 
			"`打卡时间`>=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 16:10:00") & "') And " & _
			"`打卡时间`<=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 18:10:00") & "')"
		objRS2.Fields("下班2") = StringToDate(RunSQLReturnString(objConn1, strSQL))
		
		strSQL = "Select `打卡时间` From [" & GetFileNameEx(dbSource, "FN") & "] Where `姓名`='" & objRS2.Fields("姓名") & "' And " & _ 
			"`打卡时间`>=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 18:30:00") & "') And " & _
			"`打卡时间`<=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 19:10:00") & "')"
		objRS2.Fields("上班3") = StringToDate(RunSQLReturnString(objConn1, strSQL))
		
		strSQL = "Select `打卡时间` From [" & GetFileNameEx(dbSource, "FN") & "] Where `姓名`='" & objRS2.Fields("姓名") & "' And " & _ 
			"`打卡时间`>=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 21:00:00") & "') And " & _
			"`打卡时间`<=CDate('" & StringToDate(objRS2.Fields("打卡日期") & " 22:00:00") & "')"
		objRS2.Fields("下班3") = StringToDate(RunSQLReturnString(objConn1, strSQL))
		
		' ' '
复制代码