實(shí)現(xiàn)考勤數(shù)據(jù)轉(zhuǎn)換的SQL存儲(chǔ)過程設(shè)計(jì)

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

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

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

1 考勤記錄的判別分析

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

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

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

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

2 程序設(shè)計(jì)

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

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

2.2 設(shè)計(jì)思路

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

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

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）

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

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）

這樣處理后，標(biāo)識(shí)為2的是上班時(shí)間，標(biāo)識(shí)為3的是下班時(shí)間。

2.3 程序改進(jìn)

上述代碼雖然能夠標(biāo)識(shí)出上下班時(shí)間，但是是一次性的，運(yùn)行過程中還有一些情況需要進(jìn)行處置。

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

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

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

改進(jìn)后的程序代碼如下：

第1步，將所有新增記錄標(biāo)識(shí)為待處理狀態(tài)，即把done由0變?yōu)?。為防止程序沒有執(zhí)行完意外中斷，保證數(shù)據(jù)統(tǒng)計(jì)的正確性，此步可以同時(shí)將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ù)中，取下班前的最小值標(biāo)識(shí)為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ù)中，取上班后的最大值標(biāo)識(shí)為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ù)標(biāo)識(shí)為無(wú)效，即把done由1變?yōu)?。

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

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

第5步，將234、235、2345中的4或5標(biāo)識(shí)為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)時(shí)間在上班后時(shí)標(biāo)識(shí)為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的都是有效值了。此時(shí)的2或4是最小值可作為上班時(shí)間，3或5是最大值可作為下班時(shí)間。取有變化的考勤記錄，把一人多條考勤記錄可以轉(zhuǎn)換成了一人一條記錄。可根據(jù)需要將相關(guān)數(shù)據(jù)導(dǎo)入其他特定表中供用戶查詢，這里只列出其中的select語(yǔ)句。

/*step7*/select distinct j.ymd as 年月日，j.ssn as 卡號(hào)，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 上班刷卡時(shí)間，

（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 下班刷卡時(shí)間

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é)束語(yǔ)

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

[參考文獻(xiàn)]

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

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

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

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