再次感谢版主。
程序代码:
CLEAR ALL CLOSE DATABASES ALL CLEAR SET SAFETY OFF SET EXACT ON SET DATE ANSI && 使用yy/mm/dd格式 SET CENTURY ON && 世纪格式yyyy SET STRICTDATE TO 1 && 严格日期格式 SET HOURS TO 24 cInTime = "19:00" cOutTime = "22:30" CreateTargetDBF() USE TargetDB INDEX TargetDB EXCLUSIVE IN 0 SELECT TargetDB cID = "" SCAN ALL IF cID != TargetDB.id cID = TargetDB.id nCount = 0 ELSE nCount = nCount + 1 ENDIF IF nCount > 0 SKIP -nCount tTime1 = TargetDB.checktime REPLACE TargetDB.time1 WITH "", TargetDB.time2 WITH "", TargetDB.time3 WITH "" SKIP nCount tTime2 = TargetDB.checktime REPLACE TargetDB.time1 WITH "", TargetDB.time2 WITH "" REPLACE TargetDB.time3 WITH GetTimeString(tTime2 - tTime1) ELSE tTime1 = DATETIME(YEAR(TargetDB.checktime), MONTH(TargetDB.checktime), DAY(TargetDB.checktime), VAL(LEFT(cInTime,2)), VAL(RIGHT(cInTime,2))) tTime2 = DATETIME(YEAR(TargetDB.checktime), MONTH(TargetDB.checktime), DAY(TargetDB.checktime), VAL(LEFT(cOutTime,2)), VAL(RIGHT(cOutTime,2))) tTime = IIF(TargetDB.checktime > tTime2, tTime2, TargetDB.checktime) REPLACE TargetDB.time1 WITH GetTimeString(tTime - tTime1) REPLACE TargetDB.time2 WITH GetTimeString(tTime2 - tTime) ENDIF ENDSCAN BROWSE USE IN TargetDB CLOSE DATABASES ALL CLEAR ALL RETURN PROCEDURE CreateTargetDBF() USE 111 ALIAS SourceDB EXCLUSIVE IN 0 INDEX ON userid TO 111 SELECT SourceDB COPY STRUCTURE TO TargetDB ALTER TABLE TargetDB ADD COLUMN id C(11) ALTER TABLE TargetDB ADD COLUMN time1 C(8) && 打卡时间与标准上班时间之差 ALTER TABLE TargetDB ADD COLUMN time2 C(8) && 标准下班时间与打卡时间之差 ALTER TABLE TargetDB ADD COLUMN time3 C(8) && 有完整上下班打卡记录的时间 SELECT TargetDB INDEX ON id TO TargetDB SELECT SourceDB GOTO TOP SCAN ALL IF SUBSTR(TTOC(SourceDB.checktime), 12, 5) >= cInTime SELECT TargetDB APPEND BLANK REPLACE TargetDB.userid WITH SourceDB.userid REPLACE TargetDB.checktime WITH SourceDB.checktime REPLACE TargetDB.id WITH PADL(SourceDB.userid, 3, '0') + CHRTRAN(LEFT(TTOC(SourceDB.checktime), 10), '.', '') ENDIF SELECT SourceDB ENDSCAN USE IN SourceDB USE IN TargetDB ENDPROC FUNCTION GetTimeString(tnSeconds) LOCAL lcString lcString = "" DO WHILE tnSeconds > 0 lcString = PADL(MOD(tnSeconds, 60), 2, '0') + lcString tnSeconds = INT(tnSeconds / 60) IF tnSeconds > 0 lcString = ":" + lcString ENDIF ENDDO RETURN lcString ENDFUNC
得到這些數據之後,怎麼取捨,你自己應該能夠做下去了,後面我就不寫啦。自己修改一下程序,那代碼應該不難理解。