摘 要：在考勤系統(tǒng)中，考勤機會記錄下某人所有刷卡時間，而面對一條或多條考勤記錄，我們還需要一個對比判斷的過程來確定哪些記錄是有效的上下班考勤記錄。本文根據(jù)我?？记谝?guī)則設(shè)計了一個SQL存儲過程，能夠排除無效考勤記錄，及時生成準確有效的上下班考勤記錄，提供給用戶網(wǎng)上查詢。

關(guān)鍵詞：考勤；SQL；存儲過程

學(xué)校要求教員干部上下班必須刷卡考勤，規(guī)定晚于8：00刷卡為遲到，早于17：40刷卡為早退。目前，我校建有多個考勤機，24小時供教員干部刷卡考勤。但考勤機中只記錄了某人刷卡時間，至于哪些記錄是有效的，該記錄是上班還是下班，沒有實現(xiàn)智能判斷。為便于校園網(wǎng)上及時查詢，需要設(shè)計一段程序來智能判別有效上下班記錄，并自動把一人多條考勤記錄合成一人一條記錄。

1 考勤記錄的判別分析

當(dāng)天考勤記錄的各種情況可以通過下表來顯示：

當(dāng)某用戶只有1條考勤記錄時，考勤時間最大值T_max=最小值T_min，上班考勤時間Tcome和下班考勤時間Tgo的值會有2種情況：①“考勤記錄時間<下班時間”，則Tcome=T_min，Tgo為空；②“考勤記錄時間>=下班時間”，則Tcome為空，Tgo=T_max。

當(dāng)某用戶有2條以上考勤記錄時，考勤時間存在最大值T_max和最小值T_min，上班考勤時間Tcome和下班考勤時間Tgo的值會有3種情況：①“T_max<=上班時間且T_min<=上班時間”，Tcome=T_min，Tgo為空；②“T_max>上班時間且T_min<下班時間”，Tcome=T_min，Tgo=T_max；③“T_max>=下班時間且T_min>=上班時間”，Tcome為空，Tgo=T_max。

通過上述分析可以看出，用戶當(dāng)天不管是只有一條考勤記錄還是有多條記錄，要判別出哪條記錄是上班還是下班，確實比較復(fù)雜。如果簡單地用最大值和最小值分別代替上班考勤時間和下班考勤時間，是不能真實反映出考勤狀態(tài)的。另外，在數(shù)據(jù)量比較大的情況下，用MIN和MAX函數(shù)進行統(tǒng)計，系統(tǒng)需要占用大量資源，查詢效率比較低。所以，有必要設(shè)計一個考勤數(shù)據(jù)轉(zhuǎn)換程序，把一人多條考勤記錄轉(zhuǎn)換成包含上下班時間的一人一條記錄，提高查詢效率。

2 程序設(shè)計

2.1 考勤表數(shù)據(jù)結(jié)構(gòu)

創(chuàng)建SQL考勤表jqr_inout，從各考勤機獲取的考勤數(shù)據(jù)都匯總到此表中，字段有：

2.2 設(shè)計思路

通過程序處理，把有效最小值和最大值的數(shù)據(jù)標識出來，進而識別出有效的上下班考勤時間。表中字段done，表示處理狀態(tài)，默認值為0。其中，0表示未處理的新建記錄，2為最小值即上班時間，3為最大值即下班時間。

首先在下班前的時間中取出有效的最小值，即上班時間。

update jqr_inout set done=2 where done=0 and hm<'1740' and checktime=（select min（ja.checktime） from jqr_inout ja where ja.ssn=jqr_inout.ssn and ja.ymd= jqr_inout.ymd）

接著從上班后的時間中取出有效的最大值，即下班時間。

update jqr_inout set done=3 where done=0 and hm>'0800' and checktime=（select max（ja.checktime） from jqr_inout ja where ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）

這樣處理后，標識為2的是上班時間，標識為3的是下班時間。

2.3 程序改進

上述代碼雖然能夠標識出上下班時間，但是是一次性的，運行過程中還有一些情況需要進行處置。

一是考勤數(shù)據(jù)是實時采集的，數(shù)據(jù)會不斷地增加進來，需要把已處理過的記錄和待處理的記錄以及處理中的記錄區(qū)分開來。我們可以有字段done來表示，1-3表示正在處理的記錄（1為未確定狀態(tài)，2為最小值，3為最大值），4-9表示已經(jīng)處理過的記錄（4為最小值，5為最大值，9為無效值）。

二是隨著考勤數(shù)據(jù)的增加，最小值和最大值也會發(fā)生變化，原來統(tǒng)計的最小值或最大值有可能不準確，需要對已處理過的最小值和最大值進行有效性判別。

三是程序在執(zhí)行中有可能會意外中斷，需要重新統(tǒng)計。

改進后的程序代碼如下：

第1步，將所有新增記錄標識為待處理狀態(tài)，即把done由0變?yōu)?。為防止程序沒有執(zhí)行完意外中斷，保證數(shù)據(jù)統(tǒng)計的正確性，此步可以同時將done由2或3變?yōu)?。此步執(zhí)行后，done的值可能有1、4、5、9。

/*step1*/update jqr_inout set done = 1 where done in （0，2，3）

第2步，在done的值為1的數(shù)據(jù)中，取下班前的最小值標識為2，即把done由1變?yōu)?。此步執(zhí)行后，done的值可能有1、2、4、5、9。

/*step2*/update jqr_inout set done=2 where done=1 and hm<'1740' and checktime=（select min（ja.checktime） from jqr_inout ja where ja.done not in （0，9） and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）

第3步，在done的值為1的數(shù)據(jù)中，取上班后的最大值標識為3，即把done由1變?yōu)?。此步執(zhí)行后，done的值可能有1、2、3、4、5、9。

/*step3 */update jqr_inout set done=3 where done=1 and hm>'0800' and checktime=（select max（ja.checktime） from jqr_inout ja where ja.done not in （0，9） and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）

第4步，把done的值為1的數(shù)據(jù)標識為無效，即把done由1變?yōu)?。

/*step4*/update jqr_inout set done=9 where done =1

此步執(zhí)行后，done的值可能有2、3、4、5、9。某天某用戶的考勤數(shù)據(jù)組合情況如下表。其中有7種情況需要標識出無效值，在第5步和第6步中分2步完成。

第5步，將234、235、2345中的4或5標識為9，將35、345中的5設(shè)為9，將245中的4設(shè)為9。

/*step5*/update jqr_inout set done=9

where done in （4，5） and （select count（*） from jqr_inout ja where ja.done in （2，3）

and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd）=2

or done=5 and （select count（*） from jqr_inout jc where jc.done in （3，5）

and jc.ssn=jqr_inout.ssn and jc.ymd=jqr_inout.ymd） =2

or done=4 and （select count（*） from jqr_inout jb where jb.done in （2，4，5）

and jb.ssn=jqr_inout.ssn and jb.ymd=jqr_inout.ymd） =3

第6步，將24中的4，當(dāng)時間在上班后時標識為3，否則為9。

/*step6*/update jqr_inout set done =（case when hm>’0800’ then 3 else 9 end）

where done=4 and （select count（*） from jqr_inout ja where ja.done in （2，4）

and ja.ssn=jqr_inout.ssn and ja.ymd=jqr_inout.ymd） = 2

第7步，上述2步執(zhí)行后，done的值為2、3、4、5的都是有效值了。此時的2或4是最小值可作為上班時間，3或5是最大值可作為下班時間。取有變化的考勤記錄，把一人多條考勤記錄可以轉(zhuǎn)換成了一人一條記錄?？筛鶕?jù)需要將相關(guān)數(shù)據(jù)導(dǎo)入其他特定表中供用戶查詢，這里只列出其中的select語句。

/*step7*/select distinct j.ymd as 年月日，j.ssn as 卡號，j.name as 姓名，

（select ja.checktime from jqr_inout ja where ja.ssn=j.ssn and ja.ymd=j.ymd

and ja.done in （2，4）） as tcome as 上班刷卡時間，

（select jb.checktime from jqr_inout jb where jb.ssn=j.ssn and jb.ymd=j.ymd

and jb.done in （3，5）） as tgo as 下班刷卡時間

from jqr_inout j where exists （select 1 from jqr_inout jc where jc.ssn=j.ssn

and jc.ymd=j.ymd and jc.done in （2，3）） order by j.ssn，m.wid

第8步，將2改為4，3改為5。完成數(shù)據(jù)轉(zhuǎn)換處理過程。

/*step8*/update jqr_inout set done=（case when done=2 then 4 else 5 end）

where done in （2，3）

3 結(jié)束語

本文設(shè)計的SQL存儲過程，通過在考勤記錄表中增加一個表示處理狀態(tài)的字段done，然后經(jīng)過一系列的數(shù)據(jù)對比處理，最終把有效的最小值和最大值標識出來，進而識別出有效的上下班考勤時間。在我?？记谙到y(tǒng)中，我們把該存儲過程放在相關(guān)程序中定期運行，實現(xiàn)了教員干部在網(wǎng)上及時準確地查詢考勤信息的功能，取得了比較好的效果。

[參考文獻]

[1]嚴蘇丹.基于校園局域網(wǎng)的學(xué)生智能考勤系統(tǒng)研究與開發(fā).科技信息，2012，6.

[2]邵東軼.基于web的考勤管理系統(tǒng)的設(shè)計與開發(fā).計算機光盤軟件與應(yīng)用，2012，15.

[3]周勣.基于網(wǎng)絡(luò)的考勤系統(tǒng)開發(fā)及應(yīng)用.中國衛(wèi)生資源，2012，1.

[4]萬瑤，姚娟，趙文強.自動考勤系統(tǒng)的設(shè)計與實現(xiàn)，山西電子技術(shù)，2011，5.