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 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,
 StartHour char(3)not null,
 EndDay char(2)not null,
 EndHour char(2)not null,
 Reason text,
 CardID smallint not null)
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)
)
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
  @sday smalldatetime,
  @eday smalldatetime,
  @card smallint
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