一個簡單的考勤系統 (轉)

一個簡單的考勤系統 (轉)[@more@]

不考慮加班，請假，外出之類。

原始記錄表:
1：工作日曆表calendar
2：排班表worktime
3：人事資料表employee
4：異常類別表(遲到，早退，無刷卡...)abnoty
5：考勤記錄表timecard

處理結果表：(可以用表或檢視的形式存放)
1: 打卡異常  10001  1102  上午上班  遲到
2：考勤明細表1：  10001  20031101  07:55/12:01/12:02/13:55/18:00/18:01  列出所有打卡明細
3：考勤明細表2：  10001  20031101  07:55  12:01  13:55  18:00

 

/*工作日曆表：
如下假設只有三種工作日曆，預設情況如下
1：星期六和星期天不上班
2：星期天不上班
4：每天都上班
*/
create table calendar(
 date smalldatetime primary key clustered,
 flag1 bit,
 flag2 bit,
 flag3 bit
)
exec sp_addextendedproperty N'MS_Description', N'日期', N'user', N'o', N'table', N'calendar', N'column', N'date'
exec sp_addextendedproperty N'MS_Description', N'是否上班1', N'user', N'dbo', N'table', N'calendar', N'column', N'flag1'
exec sp_addextendedproperty N'MS_Description', N'是否上班2', N'user', N'dbo', N'table', N'calendar', N'column', N'flag2'
exec sp_addextendedproperty N'MS_Description', N'是否上班3', N'user', N'dbo', N'table', N'calendar', N'column', N'flag3'
/* 個別錄入資料
insert calendar '20031101',0,1,1
union all select '20031102',0,0,1
union all select '20031103',1,1,1
union all select '20031104',1,1,1
*/

/*********如下過程用於批次錄入資料**************/
create  Procedure AddWorkCalendar @BDate SmallDateTime,@EDate SmallDateTime
As
 Declare @CDate SmallDateTime
 Declare @Flag1 Bit
 Declare @Flag2 Bit
 Declare @Flag3 Bit
 
 Set @CDate = @BDate
 If Exists (Select * from calendar Where cld_rq Between @BDate And @EDate)
 Begin
 Raiserror('已有該範圍的資料，請核對開始日期和結束日期!!!',16,-1)
 Return
 End
 While @CDate <= @EDate
 Begin
 Set @Flag1 = (Case When DatePart(WeekDay,@CDate) in (1,7) then 0 Else 1 End)
 Set @Flag2 = (Case When DatePart(WeekDay,@CDate)=1 then 0 Else 1 End)
 Set @Flag3 = 1
 Insert calendar Values(@CDate,@Flag,@Flag1,@Flag2)
 Set @CDate = DateAdd(Day,1,@CDate)
 End

/********  Usage:
  Exec AddWorkCalendar '20031101','20031130'
*********/

/*************
當然，更靈活的方法是如下方式建表：
日期　　　方式　　　標誌
20011101  1  0
20011101  2  1
20011101  3  1
...
********************/

 

/*
2:排班表(為簡單起見，不考慮加班的排班，且只考慮一天最多需四次刷卡的情況)
*/
create table worktime(
 worktimeid int primary key clustered,
 minb char(5),
 mins char(5),
 mine char(5),
 moutb char(5),
 mouts char(5),
 moute char(5),
 ainb char(5),
 ains char(5),
 aine char(5),
 aoutb char(5),
 aouts char(5),
 aoute char(5)
)
go
exec sp_addextendedproperty N'MS_Description', N'班制編號', N'user', N'dbo', N'table', N'worktime', N'column', N'worktimeid'
exec sp_addextendedproperty N'MS_Description', N'上午上班始', N'user', N'dbo', N'table', N'worktime', N'column', N'minb'
exec sp_addextendedproperty N'MS_Description', N'上午上班標準', N'user', N'dbo', N'table', N'worktime', N'column', N'mins'
exec sp_addextendedproperty N'MS_Description', N'上午上班止', N'user', N'dbo', N'table', N'worktime', N'column', N'mine'
exec sp_addextendedproperty N'MS_Description', N'上午下班始', N'user', N'dbo', N'table', N'worktime', N'column', N'moutb'
exec sp_addextendedproperty N'MS_Description', N'上午下班標準', N'user', N'dbo', N'table', N'worktime', N'column', N'mouts'
exec sp_addextendedproperty N'MS_Description', N'上午下班止', N'user', N'dbo', N'table', N'worktime', N'column', N'moute'

exec sp_addextendedproperty N'MS_Description', N'下午上班始', N'user', N'dbo', N'table', N'worktime', N'column', N'ainb'
exec sp_addextendedproperty N'MS_Description', N'下午上班標準', N'user', N'dbo', N'table', N'worktime', N'column', N'ains'
exec sp_addextendedproperty N'MS_Description', N'下午上班止', N'user', N'dbo', N'table', N'worktime', N'column', N'aine'
exec sp_addextendedproperty N'MS_Description', N'下午下班始', N'user', N'dbo', N'table', N'worktime', N'column', N'aoutb'
exec sp_addextendedproperty N'MS_Description', N'下午下班標準', N'user', N'dbo', N'table', N'worktime', N'column', N'aouts'
exec sp_addextendedproperty N'MS_Description', N'下午下班止', N'user', N'dbo', N'table', N'worktime', N'column', N'aoute'
/*
注：在上班始－上班標準間打卡屬於正常打卡，在上班標準－上班止間打卡屬於遲到
　　在下班始－下班標準間打卡屬於早退，在下班標準－下班止間打卡屬於正常打卡
　　其餘時間打卡屬於無效打卡。
*/

--3：人事資料表(只列出與考勤有關的欄位)
create table employee(
 workno char(6) primary key,
 [name] varchar(10),
 calendarFlag int check(calendarFlag in (1,2,3) ),  -- 1,2，3分別表示工作日曆中的標誌1，標誌2，標誌3
 worktimeid int  -- 排班表中的班制
)
go
exec sp_addextendedproperty N'MS_Description', N'工號', N'user', N'dbo', N'table', N'employee', N'column', N'workno'
exec sp_addextendedproperty N'MS_Description', N'姓名', N'user', N'dbo', N'table', N'employee', N'column', N'name'
exec sp_addextendedproperty N'MS_Description', N'工作日曆方式', N'user', N'dbo', N'table', N'employee', N'column', N'calendarFlag'
exec sp_addextendedproperty N'MS_Description', N'班制', N'user', N'dbo', N'table', N'employee', N'column', N'worktimeid'

/*
calendarFlag  1,2，3分別表示工作日曆中的標誌1，標誌2，標誌3
worktimeid  對應排班表中的班制
*/

--4：異常類別表abnormity
create table abnormity(
 abnormityno int primary key clustered,
 [description] varchar(10)
)
go
exec sp_addextendedproperty N'MS_Description', N'異常類別', N'user', N'dbo', N'table', N'abnormity', N'column', N'abnormityno'
exec sp_addextendedproperty N'MS_Description', N'異常說明', N'user', N'dbo', N'table', N'abnormity', N'column', N'description'

/**************
異常包括:遲到(還可細分成遲到的時間段)，早退，無刷卡...
當然可能還有扣薪方式之類的，此處不予考慮。
*****************/

 

 

5：考勤記錄表timecard
create table timecard(
 workno char(6),
 [date] datetime,
 constraint [pk_timecard] primary key clustered
 (
 workno,
 [date]
 )  ON [PRIMARY]
)
go
exec sp_addextendedproperty N'MS_Description', N'工號', N'user', N'dbo', N'table', N'timecard', N'column', N'workno'
exec sp_addextendedproperty N'MS_Description', N'打卡時間', N'user', N'dbo', N'table', N'timecard', N'column', N'date'

/*******
其實我實際中是按卡號（對應工號），日期，時間，卡鍾號　　四個欄位來存的。
*******/

 

/****************如下處理得到　考勤明細表1：**********/
--1.建立一個合併的
create  function f_kqlist(@workno char(6),@date char(8))
returns varchar(400)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+'/'+ convert(char(8),[date],108) from timecard
 where workno = @workno and datediff(day,[date], @date) = 0
set @str=right(@str,len(@str)-1)
return(@str)
End

/*  Usage: 
  select distinct workno,date,dbo.f_kqlist(workno,[date])
  from timecard
 where workno = '102337' and convert(char(8),[date],112) = '20030814'
*/

剩下的留給大家吧。其實也不難，就看誰的比較高一些罷了。  :D
(我的做法：　 異常處理  :  用儲存過程，用遊標做的，懶得去了
 　　　 考勤明細表2：　用函式，原來用遊標，不好，後來才改用函式的)