create_table.sql

VB写的通过串口与考勤机连接通讯的程序

create database ccLanYa_KaoQin
go

use ccLanYa_KaoQin
go

create table BuMen (Name char(20))
go

create table Worker
(
 CardID smallint not null,
 Name varchar(14)not null,
 Permit varchar(8)not null,
 Password varchar(8)not null,
 BuMen varchar(14)not null,
 primary key (CardID)
)
go

create table Card(CardID smallint,NeiMa char(8)not null)

go

create table Login(Name char(20),PassWord char(12)) 
go

create table GongXiu(Name varchar(16),StarDay smalldatetime,EndDay smalldatetime)
go

create table KaoQin 
(
     KaoQinID int identity (1,1)not null,
     CardID smallint not null,
     Date smalldatetime not null,
     Hour char(2)not null,
     Minute char(2)not null,
     Second char(2)not null,
     Type varchar(10)not null,
     primary key(KaoQinID)
)
go

create table KaoQin_temp 
(    Name varchar(10),
      CardID smallint ,
     Date smalldatetime ,
     Hour char(2),
     Minute char(2),
     Second char(2),
     Type varchar(10),
     Stday smalldatetime,
     Enday smalldatetime
     
)


go

CREATE TABLE KaoQinSource 
(	KaoQinID int IDENTITY (1, 1) NOT NULL ,
	CardID smallint NOT NULL ,
	Date smalldatetime NOT NULL ,
	Hour char (2)  NOT NULL ,
	Minute char (2)   NOT NULL ,
	Second char (2)   NOT NULL ,
	Type varchar (10)  NOT NULL ,
        PRIMARY key (KaoQinID))
go

create table Time 
(UpHour char(2)not null,
 UpMin  char(2)not null,
 DownHour char(2)not null,
 DownMin char(2)not null,
 Type varchar(10)not null,
 primary key (Type))
go

create table QingJia
(QingJiaID int identity(1,1)not null,
 CardID smallint not null,
 Name varchar(14)not null,
 StartDay char(2)not null,
 EndDay char(2)not null,
 Reason text)
go


create table Count_table
( 姓名 varchar(8),
  迟到 varchar(2),
  早退 varchar(2),
  加班  varchar(2),
  节日加班 varchar(2),
  请假 varchar(2),
  请假半天 varchar(2),
  旷勤 varchar(2),
  旷勤半天 varchar(2),
  出差 varchar(2),
  Stday smalldatetime,
  Enday smalldatetime,
  sCount varchar(3),
  sGong varchar(3),
  sShi varchar(3)
)
go

create table Lastday(Lastday smalldatetime)
go

create index Index_Worker on Worker(CardID)
go

create index Index_KaoQin on KaoQin(Type)
go

create proc Count_proc
  @card smallint,
  @sday smalldatetime,
  @eday smalldatetime
 
as
select count(*)as countstr,Type from kaoqin  
where  Cardid=@card and Date between @sday and @eday 
group by type
go

create proc Card_proc 
as
select distinct CardID from Worker order by CardID
go

create proc DayIN_proc
    @sday smalldatetime,
    @eday smalldatetime
as
select distinct Date from KaoQinSource where 
Date in (select StarDay from GongXiu) and Date between
@sday and @eday
go

create proc DayNot_proc
    @sday smalldatetime,
    @eday smalldatetime
as
select distinct Date from KaoQinSource where 
Date not in (select StarDay from GongXiu) and Date between
@sday and @eday
go

create proc Hour_proc
 @card smallint,
 @day smalldatetime
as 
select * from KaoQinSource where Hour<'12' and CardID=@card and Date=@day
go
create proc InsertKS_proc
@card smallint,
@day smallint,
@hour varchar(2),
@minute varchar(2),
@second varchar(2),
@type varchar(10)
as
insert into KaoQinSource 
values( @card,@day,@hour,@minute,@second,@type)
go


create proc InsertK_proc
@card smallint,
@day smalldatetime,
@hour varchar(2),
@minute varchar(2),
@second varchar(2),
@type varchar(10)
as
insert into KaoQin 
values( @card,@day,@hour,@minute,@second,@type)
go


create proc GongXiu_proc
as
select Name as 假期名称, StarDay as 假期时间 from 

go
GongXiu order by Name,StarDay

go
create proc GongName_proc
as
select distinct Name from GongXiu
go

create proc QingJia_proc
as 
select QingJiaID as 编号,Name as 姓名,
StartDay as 开始时间,EndDay as 结束时间,
Type as 类别,Reason as 备注 from QingJia 
order by QingJiaID
go

create proc QingName_proc
 @name varchar(10)
as
select CardID from Worker where Name=@name
go

create proc insertcount_proc 
 @name varchar(10),@chi varchar(2),
@zao varchar(2), @jia varchar(2),
@jieri varchar(2),@qing varchar(2),
 @qingban varchar(2),@kuang varchar(2),
 @kuangban varchar(2),@chu varchar(2),
 @stday smalldatetime,@endday smalldatetime,
 @count varchar(3),@gong varchar(3),@shi varchar(3)
as
 insert into count_table values(@name,@chi,@zao,
 @jia,@jieri,@qing,@qingban,@kuang,@kuangban,@chu,
 @stday,@endday,@count,@gong,@shi)

GO


create proc showcount_proc
 as 
   select * from count_table
go


create proc KSDI_prod
@day smalldatetime,
@id smallint
as
select * from KaoQinSource where  
Date=@day and CardID =@id 
order by KaoQinID
go