--員工考勤記錄修改日誌
CREATE TABLE CHECKEXACT (
        EXACTID INT IDENTITY(1,1) NOT NULL ,    --修改日誌ID
	USERID INT NULL DEFAULT 0,              --員工ID號 
	CHECKTIME DATETIME NULL DEFAULT 0,      --簽到/簽退時間
	CHECKTYPE VARCHAR (2) NULL DEFAULT 0,   --原來的簽到/簽退標誌：I-簽到，O-簽退
	ISADD SMALLINT NULL DEFAULT 0,          --是否新增的記錄
	YUYIN VARCHAR (25) NULL ,               --修改考勤記錄的原因
	ISMODIFY SMALLINT NULL DEFAULT 0,       --是否僅僅是修改原始記錄
	ISDELETE SMALLINT NULL DEFAULT 0,       --是否是刪除的記錄
	INCOUNT SMALLINT NULL DEFAULT 0,        --是否恢復
	ISCOUNT SMALLINT NULL DEFAULT 0,        --
	MODIFYBY VARCHAR (20) NULL,             --操作員
	[DATE] DATETIME NULL,                   --操作時間          
        CONSTRAINT EXACTID PRIMARY KEY (EXACTID)
)
;

--簽到/簽退記錄表
CREATE TABLE CHECKINOUT (               
	USERID INT NOT NULL ,                              --員工ID號     
	CHECKTIME DATETIME NOT NULL DEFAULT GETDATE(),     --簽到/簽退時間  
	CHECKTYPE VARCHAR (1) NULL DEFAULT 'I',            --簽到/簽退標誌：I-簽到，O-簽退
	VERIFYCODE INT NULL DEFAULT 0,                     --驗證方式：除256的餘數表示簽到的方式: 0-密碼；1-指紋；2-卡。大於256表示該記錄已經被確認，在於處理時不進行處理
	SENSORID VARCHAR (5) NULL,                         --採集資料的考勤終端/設備ID
        CONSTRAINT USERCHECKTIME PRIMARY KEY (USERID, CHECKTIME)
)
;

--部門表
CREATE TABLE DEPARTMENTS (                                 
	DEPTID INT IDENTITY(1,1) NOT NULL ,      --部門ID
	DEPTNAME VARCHAR (30) NULL ,             --部門名稱
	SUPDEPTID INT NOT NULL DEFAULT 1,        --上級部門的ID  
	CONSTRAINT DEPTID PRIMARY KEY (DEPTID)
)
;

CREATE TABLE EXCNOTES (              
	USERID INT NULL ,
	ATTDATE DATETIME NULL ,
	NOTES VARCHAR (200) NULL
)  
;

--節假日表
CREATE TABLE HOLIDAYS (                            
	HOLIDAYID INT IDENTITY(1,1) NOT NULL ,   
	HOLIDAYNAME VARCHAR (20) NULL ,            
	HOLIDAYYEAR SMALLINT NULL ,                
	HOLIDAYMONTH SMALLINT NULL ,               
	HOLIDAYDAY SMALLINT NULL DEFAULT 1,                 
	STARTTIME DATETIME NULL ,                  
	DURATION SMALLINT NULL ,                   
	HOLIDAYTYPE SMALLINT NULL ,                
	XINBIE VARCHAR (4) NULL ,                     
	MINZU VARCHAR (50) NULL,                       
        CONSTRAINT HOLID PRIMARY KEY (HOLIDAYID)
)
;

--班次表
CREATE TABLE NUM_RUN (                              
	NUM_RUNID INT IDENTITY(1,1) NOT NULL ,      --班次ID號
	OLDID INT NULL DEFAULT -1,                  --          
	NAME VARCHAR (30) NOT NULL ,                --班次名稱
	STARTDATE DATETIME NULL DEFAULT '2000-1-1', --起用日期                  
	ENDDATE DATETIME NULL DEFAULT '2099-12-31', --結束使用日期                    
	CYLE SMALLINT NULL DEFAULT 1,               --班次一次循環的週期         
	UNITS SMALLINT NULL DEFAULT 1,              --週期單位
        CONSTRAINT NUMID PRIMARY KEY (NUM_RUNID)
)
;

--班次排班時段表
CREATE TABLE NUM_RUN_DEIL (                      
	NUM_RUNID SMALLINT NOT NULL ,           --班次的ID號     
	STARTTIME DATETIME NOT NULL ,           --開始時間     
	ENDTIME DATETIME NULL ,                 --結束時間 
	SDAYS SMALLINT NOT NULL ,               --開始日期     
	EDAYS SMALLINT NULL ,                   --結束日期
        SCHCLASSID INT NULL DEFAULT -1,         --時段所屬的類別
        CONSTRAINT NUMID2 PRIMARY KEY (NUM_RUNID, SDAYS, STARTTIME)
)
;

--管理員權限設定表
CREATE TABLE SECURITYDETAILS (                 
	SECURITYDETAILID INT IDENTITY(1,1) NOT NULL ,
	USERID SMALLINT NULL ,
	DEPTID SMALLINT NULL ,
	SCHEDULE SMALLINT NULL ,
	USERINFO SMALLINT NULL ,
	ENROLLFINGERS SMALLINT NULL ,
	REPORTVIEW SMALLINT NULL ,
	REPORT VARCHAR (10) NULL,
        CONSTRAINT NAMEID2 PRIMARY KEY (SECURITYDETAILID)
)  
;

--輪班表
CREATE TABLE SHIFT (                           
	SHIFTID INT IDENTITY(1,1) NOT NULL ,            --輪班ID號
	NAME VARCHAR (20) NULL ,                        --輪班名稱
	USHIFTID INT NULL DEFAULT -1,                   --  
	STARTDATE DATETIME NOT NULL DEFAULT '1900-1-1', --起用輪班的日期          
	ENDDATE DATETIME NULL DEFAULT '1900-12-31',     --結束時用輪班的日期            
	RUNNUM SMALLINT NULL DEFAULT 0,                 --該輪班中所含的班次數 0<x<13
	SCH1 INT NULL DEFAULT 0,                        --輪班的第一個班次 
	SCH2 INT NULL DEFAULT 0,                        --輪班的第二個班次 
	SCH3 INT NULL DEFAULT 0,                        --輪班的第三個班次
	SCH4 INT NULL DEFAULT 0,                        --輪班的第四個班次
	SCH5 INT NULL DEFAULT 0,                        --輪班的第五個班次
	SCH6 INT NULL DEFAULT 0,                        --輪班的第六個班次
	SCH7 INT NULL DEFAULT 0,                        --輪班的第七個班次
	SCH8 INT NULL DEFAULT 0,                        --輪班的第八個班次
	SCH9 INT NULL DEFAULT 0,                        --輪班的第九個班次
	SCH10 INT NULL DEFAULT 0,                       --輪班的第十個班次
	SCH11 INT NULL DEFAULT 0,                       --輪班的第十一個班次
	SCH12 INT NULL DEFAULT 0,                       --輪班的第十二個班次
	CYCLE SMALLINT NULL DEFAULT 0,                  --輪班週期
	UNITS SMALLINT NULL DEFAULT 0 ,                 --週期單位
        CONSTRAINT SHIFTS PRIMARY KEY (SHIFTID)
)  
;

--員工登記指紋表
CREATE TABLE TEMPLATE (                           
	TEMPLATEID INT IDENTITY(1,1) NOT NULL ,    --指紋ID號
	USERID INT NOT NULL ,                      --員工ID號
	FINGERID INT NOT NULL ,                    --手指ID號，0-9分別表示右手拇指、右手食指、……、左手無名指、左手小指等
	TEMPLATE image NOT NULL ,                  --指紋範本1
	TEMPLATE2 image NULL ,                     --指紋範本2
	TEMPLATE3 image NULL ,                     --指紋範本3
	BITMAPPICTURE image NULL ,                 --登記的指紋圖像1
	BITMAPPICTURE2 image NULL ,                --登記的指紋圖像2  
	BITMAPPICTURE3 image NULL ,                --登記的指紋圖像3  
	BITMAPPICTURE4 image NULL ,                --登記的指紋圖像4  
	USETYPE SMALLINT NULL ,                    --指紋範本的用法，Bit0表示僅作備份不比對，Bit1表示指紋比對時使用低辨識門限  
        CONSTRAINT TEMPLATED PRIMARY KEY (TEMPLATEID)
)
;

--員工排班表
CREATE TABLE USER_OF_RUN (              
	USERID INT NOT NULL ,                           --員工ID號
	NUM_OF_RUN_ID INT not NULL ,                    --班次或輪班ID號
	STARTDATE DATETIME not NULL DEFAULT '1900-1-1', --起用班次的日期         
	ENDDATE DATETIME not NULL DEFAULT '2099-12-31', --結束使用該班次的日期           
	ISNOTOF_RUN INT NULL DEFAULT 0,                 --是否輪班 
	ORDER_RUN INT NULL ,                            --輪班的起始班次
        CONSTRAINT USER_ST_NUM PRIMARY KEY (USERID, NUM_OF_RUN_ID, STARTDATE, ENDDATE)
)
;

--員工考勤例外（請假/公出）表
CREATE TABLE USER_SPEDAY (                    			
	USERID INT NOT NULL ,                                   --員工ID號
	STARTSPECDAY DATETIME NOT NULL DEFAULT '1900-1-1',      --開始日期    
	ENDSPECDAY DATETIME NULL DEFAULT '2099-12-31',          --結束日期  
	DATEID SMALLINT not NULL DEFAULT -1,                    --例外類型，999為公出，-1為註解
	YUANYING VARCHAR (200) NULL ,                           --例外的原因
	[DATE] DATETIME NULL ,                                  --登記/輸入記錄的時間 
        CONSTRAINT USER_SEP PRIMARY KEY (USERID, STARTSPECDAY, DATEID)
)
;

--員工臨時排班表
CREATE TABLE USER_TEMP_SCH (                  
	USERID INT not NULL ,                     --員工ID號
	COMETIME DATETIME not NULL ,              --上班時間
	LEAVETIME DATETIME not NULL ,             --下班時間  
        OVERTIME INT not NULL DEFAULT 0,           --該時段中計算加班的時間 
	[TYPE] SMALLINT NULL DEFAULT 0,           --類型       
	FLAG SMALLINT NULL DEFAULT 1,             --標誌
        SCHCLASSID INT NULL DEFAULT -1,           --該時段所屬時段類別ID號，-1表示自動判別
        CONSTRAINT USER_TEMP PRIMARY KEY (USERID, COMETIME, LEAVETIME)
)
;

--員工訊息表
CREATE TABLE USERINFO (                      
	USERID INT IDENTITY(1,1) NOT NULL ,     --員工ID號
	BADGENUMBER VARCHAR (12) NOT NULL ,     --考勤號碼    
	SSN VARCHAR (20) NULL ,                 --編號
	NAME VARCHAR (20) NULL ,                --姓名
	GENDER VARCHAR (2) NULL ,               --性別
	TITLE VARCHAR (20) NULL ,               --職務
	PAGER VARCHAR (20) NULL ,               --行動電話
	BIRTHDAY DATETIME NULL ,                --生日
	HIREDDAY DATETIME NULL ,                --聘用日期
	STREET VARCHAR (40) NULL ,              --家庭住址
	CITY VARCHAR (2) NULL ,                 --市代碼
	STATE VARCHAR (2) NULL ,                --省代碼
	ZIP VARCHAR (12) NULL ,                 --郵編
	OPHONE VARCHAR (20) NULL ,              --辦公電話   
	FPHONE VARCHAR (20) NULL ,              --家庭電話
	VERIFICATIONMETHOD SMALLINT NULL ,      --驗證方式
	DEFAULTDEPTID SMALLINT NULL  DEFAULT 1, --所屬部門ID號
	SECURITYFLAGS SMALLINT NULL ,           --管理員標誌
	ATT SMALLINT NOT NULL DEFAULT 1,        --考勤有效
	INLATE SMALLINT NOT NULL DEFAULT 1,     --計遲到               
	OUTEARLY SMALLINT NOT NULL DEFAULT 1,   --計早退                
	OVERTIME SMALLINT NOT NULL DEFAULT 1,   --計加班                 
	SEP SMALLINT NOT NULL DEFAULT 1,        --             
	HOLIDAY SMALLINT NOT NULL DEFAULT 1,    --假日休息             
	MINZU VARCHAR (8) NULL ,                --民族
	[PASSWORD] VARCHAR (20) NULL ,          --密碼
	LUNCHDURATION SMALLINT NOT NULL DEFAULT 1, --有午休
        MVerifyPass VARCHAR(10) NULL,           --考勤驗證密碼
	PHOTO Image NULL,                       --照片  
        CONSTRAINT USERIDS PRIMARY KEY (USERID)
)
;


CREATE  UNIQUE  INDEX USERFINGER ON TEMPLATE(USERID, FINGERID)
;

CREATE  UNIQUE  INDEX HOLIDAYNAME ON HOLIDAYS(HOLIDAYNAME)
;

CREATE  INDEX DEPTNAME ON DEPARTMENTS(DEPTNAME)
;

CREATE  UNIQUE  INDEX EXCNOTE ON EXCNOTES(USERID, ATTDATE)
;

CREATE  UNIQUE  INDEX BADGENUMBER ON USERINFO(BADGENUMBER)
;

INSERT INTO DEPARTMENTS (DEPTNAME, SUPDEPTID) VALUES('總公司',0)
;

--假類表
Create Table LeaveClass(
  LeaveId INT Identity(1,1) not null primary key,  --假類ID號
  LeaveName VARCHAR(20) not null,                  --假類名稱
  MinUnit float not null default 1,                --最小統計單位
  Unit smallint not null default 1,                --統計單位
  RemaindProc smallint not null default 1,         --捨入控制
  RemaindCount smallint not null default 1,        --統計時累計
  ReportSymbol varchar(4) not null default '-',    --報表中的表示符號
  Deduct float not null default 0,                 --
  Color int not null default 0,                    --顯示顏色
  Classify SMALLINT NOT null default 0)            --計算類別，~bit7-是否計算為請假
;

--統計項目表
Create Table LeaveClass1(
  LeaveId INT Identity(999,1) not null primary key, --999-公出
  LeaveName VARCHAR(20) not null,
  MinUnit float not null default 1,
  Unit smallint not null default 0,
  RemaindProc smallint not null default 2,
  RemaindCount smallint not null default 1,
  ReportSymbol varchar(4) not null default '_',
  Deduct float not null default 0,
  LeaveType SMALLINT not null default 0,             --bit0-是否統計項；bit1-是否異常情況項；
  Color int not null default 0,
  Classify SMALLINT not null default 0,              --計算類別，bit0-按班次時段分類；bit1-按節假日分類
  Calc text null)                                    --計算單位
;

--班次時段類別設定表
CREATE TABLE SchClass(
  schClassid INT identity(1,1) NOT NULL PRIMARY KEY, --時段類別ID號
  schName VARCHAR(20) NOT null,                      --時段類別名稱 
  StartTime datetime NOT NULL,                       --起始時間
  EndTime datetime NOT NULL,                         --結束時間
  LateMinutes int null,                              --計遲到分鐘數
  EarlyMinutes int null,                             --計早退分鐘數 
  CheckIn int null default 1,                        --該時段上班需要簽到
  CheckOut int null default 1,                       --該時段下班需要簽退
  CheckInTime1 datetime NULL,                        --開始簽到時間  
  CheckInTime2 datetime NULL,                        --結束簽到時間      
  CheckOutTime1 datetime NULL,                       --開始簽退時間   
  CheckOutTime2 datetime NULL,                       --結束簽退時間
  Color Int NULL default 16715535,                   --表示顏色 
  AutoBind SMALLINT NULL DEFAULT 1)                  --   
;

--系統參數表
Create Table AttParam(
  PARANAME VARCHAR (20) NOT NULL Primary key,
  PARATYPE VARCHAR (2) NULL ,
  PARAVALUE VARCHAR(100) NOT NULL)
;
insert into LeaveClass(LeaveName, Unit, ReportSymbol, Color) 
  values('病假', 1, 'B', 3398744);
insert into LeaveClass(LeaveName, Unit, ReportSymbol, Color) 
  values('事假', 1, 'S', 8421631);
insert into LeaveClass(LeaveName, Unit, ReportSymbol, Color) 
  values('探親假', 1, 'T', 16744576);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('公出', 0.5, 3, 1, 1, 'G', 3, 'if(AttItem(LeaveType1)=999,AttItem(LeaveTime1),0)+if(AttItem(LeaveType2)=999,AttItem(LeaveTime2),0)+if(AttItem(LeaveType3)=999,AttItem(LeaveTime3),0)+if(AttItem(LeaveType4)=999,AttItem(LeaveTime4),0)+if(AttItem(LeaveType5)=999,AttItem(LeaveTime5),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType)
  values('正常', 0.5, 3, 1, 0, ' ', 3);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('遲到', 10, 2, 2, 1, '>', 3, 'AttItem(minLater)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('早退', 10, 2, 2, 1, '<', 3, 'AttItem(minEarly)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('請假', 1, 1, 1, 1, '假', 3, 
  'if((AttItem(LeaveType1)>0) and (AttItem(LeaveType1)<999),AttItem(LeaveTime1),0)+if((AttItem(LeaveType2)>0) and (AttItem(LeaveType2)<999),AttItem(LeaveTime2),0)+if((AttItem(LeaveType3)>0) and (AttItem(LeaveType3)<999),AttItem(LeaveTime3),0)+if((AttItem(LeaveType4)>0) and (AttItem(LeaveType4)<999),AttItem(LeaveTime4),0)+if((AttItem(LeaveType5)>0) and (AttItem(LeaveType5)<999),AttItem(LeaveTime5),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('曠工', 0.5, 3, 1, 0, '曠', 3, 'AttItem(MinAbsent)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('加班', 1, 1, 1, 1, '+', 3, 'AttItem(MinOverTime)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('節日加班', 1, 1, 0, 1, '=', 0, 'if(HolidayId(1)=1, AttItem(MinOverTime),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType)
  values('休息日', 0.5, 3, 2, 1, '-', 2);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('未簽到', 1, 4, 2, 1, '[', 2, 
  'If(AttItem(CheckIn)=null,If(AttItem(OnDuty)=null,0,if(((AttItem(LeaveStart1)=null) or (AttItem(LeaveStart1)>AttItem(OnDuty))) and AttItem(DutyOn),1,0)), 0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType, Calc)
  values('未簽退', 1, 4, 2, 1, ']', 2, 
  'If(AttItem(CheckOut)=null,If(AttItem(OffDuty)=null,0,if((AttItem(LeaveEnd1)=null) or (AttItem(LeaveEnd1)<AttItem(OffDuty)),if((AttItem(LeaveEnd2)=null) or (AttItem(LeaveEnd2)<AttItem(OffDuty)),if(((AttItem(LeaveEnd3)=null) or (AttItem(LeaveEnd3)<AttItem(OffDuty))) and AttItem(DutyOff),1,0),0),0)),0)');
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType)
  values('離職未簽到', 1, 4, 2, 1, '{', 6);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType)
  values('離職未簽退', 1, 4, 2, 1, '}', 6);
insert into LeaveClass1(LeaveName, MinUnit, Unit, RemaindProc,
  RemaindCount, ReportSymbol, LeaveType)
  values('離職', 1, 1, 2, 1, 'L', 3);

insert into AttParam(ParaName,ParaValue) values('MinsEarly',5);
insert into AttParam(ParaName,ParaValue) values('MinsLate',10);
insert into AttParam(ParaName,ParaValue) values('MinsNoBreakIn',60);
insert into AttParam(ParaName,ParaValue) values('MinsNoBreakOut',60);
insert into AttParam(ParaName,ParaValue) values('MinsNoIn',60);
insert into AttParam(ParaName,ParaValue) values('MinsNoLeave',60);
insert into AttParam(ParaName,ParaValue) values('MinsNotOverTime',60);
insert into AttParam(ParaName,ParaValue) values('MinsWorkDay',420);
insert into AttParam(ParaName,ParaValue) values('NoBreakIn',1012);
insert into AttParam(ParaName,ParaValue) values('NoBreakOut',1012);
insert into AttParam(ParaName,ParaValue) values('NoIn',1001);
insert into AttParam(ParaName,ParaValue) values('NoLeave',1002);
insert into AttParam(ParaName,ParaValue) values('OutOverTime',0);
insert into AttParam(ParaName,ParaValue) values('TwoDay',0);
insert into AttParam(ParaName,ParaValue) values('CheckInColor',16777151);
insert into AttParam(ParaName,ParaValue) values('CheckOutColor',12910591);
insert into AttParam(ParaName,ParaValue) values('DBVersion',167);
