      HƦҶԪ
create table bk_everydaytime(
emp_id int not null,/*u*/
workday char(10) null,
zb_time real null,/*Zɼ*/
jb_time real null,/*[Zɼ*/
zbsj_time real null,/*Zư*/
jbsj_time real null,/*[Zư*/
zbbj_time real null,
jbbj_time real null,
tbj real null,/*SO*/
cd_time real null,/**/
cd_int int null,/*즸*/
zt_time real null,/*h*/
zt_int int null,/*h*/
kg_time real null,/*qu*/
kg_int int null,/*qu*/
gs_time real null,/*u*/
dl_time real null,/*ݮƮɼ*/
gx_time real null,/*uɼ*/
j_time real null,
k_time real null,
gc_time real null,/*tɼ*/
meme2_time real null);
go
create table bk_kq_data(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) null,
date_t char(4) null,
check_char char(1) null,
end_char char(1) null,
empid int null)
go

create table bk_kq_dataok(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) null,
date_t char(4) null,
check_char char(1) null,
end_char char(1) null,
empid int null);
go
create table bkrepeatekq_data(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table bktimestable(
cardnum char(10) null,/*d*/
workday datetime null,/*u@*/
moringup int null,/*WȤWZdɶ*/
char1 char(1) null,/*аO1*/
moringdown int null,/*WȤUZdɶ*/
char2 char(1) null,/*аO2*/
afternoonup int null,/*UȤWZdɶ*/
char3 char(1) null,
afternoondown int null,/*UȤUZdɶ*/
char4 char(1) null,
eveningup int null,/*[ZWZdɶ*/
char5 char(1) null,
eveningdown int null,/*[ZUZdɶ*/
char6 char(1) null,
exceptionclass char(20) null,/*ҥ~Z*/
teamsid int null,/*ռ*/
empid int null);
go
create table cardnum_workday(
cardnum char(10) null,
workday char(10) null)
go
create table changesal(
id int identity(1,1) not null,
empid int null,
yjbm_name char(10) null,
ejbm_name char(10) null,
jbid int null,
jbname char(6) null,
jsjg money null,
gzjg money null,
zyjg money null,
nzjg money null,
exedate datetime not null,
ifnew bit not null,
memo char(20) null);
go
create table changeteam(
change_id int identity(1,1) not null,
from_depid int null,
to_depid int null,
from_ejdepid int null,
to_ejdepid int null,
empid int null,
begin_time datetime null,
memo char(70) null);
go
create table corpposi(
jbid int identity(1,1) not null,
jb char(4) not null,
jsfs char(6) null,
jbgz money not null,
jbxs money null,
zwjt money null,
qq money null,
position char(10) null,
memo char(10) null);
go
create table d_data_today(
data_id int  not null,
machine_num char(2) not null,
card_num char not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table dd(
yjbmid int not null,
yjbmname char(10) not null)
go
create table dtproperties(
id int  identity(1,1)not null,
objectid int null,
property varchar(64) not null,
value varchar(255) null,
lvalue image null,
version int not null,
uvalue nvarchar(255) null);
go
create table ejbm(
ejbm_id int indentity(1,1) not null,
yjbm_id int not null,
ejbmname char(16) not null,
ejbnname char(16) not null);
go
create table empdata(
emp_id int  identity(1,1) not null,
dep_id int not null,
work_num char(10)  null,
card_num char(10) null,
name char(10) null,
teamsid int null);
go
create table empdata_bk(
emp_id int not null,
dep_id int not null,
work_num char(10)  null,
card_num char(10) null,
name char(10) null,
teamsid int null);
go
create table empdata_delete(
emp_id int not null,
dep_id int not null,
work_num char(10)  null,
card_num char(10) null,
name char(10) null,
teamsid int null);
go
create table employees(
emp_id int null,
cardnum char(10) null,
ic_id char(18) null,/**/
birthday char(10) null,/*ͤ*/
sex char(2) not null,/*ʧO*/
marry char(4) not null,/*B_*/
in_date char(10) null,/*it*/
now_add char(40) null,/*{}*/
telephone char(15) null,/*qܸX*/
home_ori char(40) null,/*ye*/
hoem_add char(40) null,/*axa}*/
home_mem char(10) null,/*ax*/
home_relation char(10) null,/*axt*/
grd_sch char(40) null,/*~|*/
grd_zy char(20) null,/*M~*/
grd_date datetime null,/*~*/
degree char(10) null);/*Ǿ{*/
go
create table employees_bk(
emp_id int null,
cardnum char(10) null,
ic_id char(18) null,
birthday char(18) null,
sex char(2) not null,
marry char(4) not null,
in_date char(4) null,
now_add char(40) null,
telephone char(15) null,
home_ori char(40) null,
home_add char(40) null,
home_mem char(10) null,
home_relation char(10) null,
grd_sch char(40) null,
grd_zy char(20) null,
grd_date datetime null,
degree char(10) null)
go
create table employees_deleted(
emp_id int null,
cardnum char(10) null,
ic_id char(18) null,
birthday char(18) null,
sex char(2) not null,
marry char(4) not null,
in_date char(4) null,
now_add char(40) null,
telephone char(15) null,
home_ori char(40) null,
home_add char(40) null,
home_mem char(10) null,
home_relation char(10) null,
grd_sch char(40) null,
grd_zy char(20) null,
grd_date datetime null,
degree char(10) null)
go
create table for_v_selecterror(
check_char char(1) null,
date_ymd char(10) null,
card_num char(10) null)
go
create table emptimestable(
cardnum char(10) null,
workday datetime null,
moringup int null,
char1 char(1) null,
moringdown int null,
char2 char(1) null,
afternoonup int null,
char3 char(1) null,
afternoondown int null,
char4 char(1) null,
eveningup int null,
char5 char(1) null,
eveningdown int null,
char6 char(1) null,
exception char(20) null,
teamsid int null,
empid int null);
go
create table emptimestableok(
cardnum char(10) null,
workday datetime null,
moringup int null,
char1 char(1) null,
moringdown int null,
char2 char(1) null,
afternoonup int null,
char3 char(1) null,
afternoondown int null,
char4 char(1) null,
eveningup int null,
char5 char(1) null,
eveningdown int null,
char6 char(1) null,
exception char(20) null,
teamsid int null,
empid int null);
go
create table everydaytime(
emp_id int not null,
workday char(10) null,
zb_time real null,
jb_time real null,
zbsj_time real null,
zbsj_time real null,
jbsj_time real null,
zbbj_time real null,
jbbj_time teal null,
tbj real null,
cd_time real null,
cd_int int null,
zt_time real null,
zt_int int null,
kg_time real null,
kg_int int null,
gs_time real null,
dl_time real null,
gx_time real null,
j_time real null,
k_time real null,
gc_time real null,
meme2_time real null)
go
create table everydaytime(
emp_id int not null,
workday char(10) null,
zb_time real null,
jb_time real null,
zbsj_time real null,
zbsj_time real null,
jbsj_time real null,
zbbj_time real null,
jbbj_time teal null,
tbj real null,
cd_time real null,
cd_int int null,
zt_time real null,
zt_int int null,
kg_time real null,
kg_int int null,
gs_time real null,
dl_time real null,
gx_time real null,
j_time real null,
k_time real null,
gc_time real null,
meme2_time real null)
go

create table fk_record(
worknum char(6) null,/*u*/
item char(16) null,/*O*/
money money null,/*B*/
fs_date char(10) null,/*oͤ*/
kkyf char(10) null,/*ڤ*/
dj_date char(10) null,/*nO*/
memo char(100) null);/*ƪ`*/
go
create table fk_recordok(
worknum char(6) null,/*u*/
item char(16) null,/*O*/
money money null,/*B*/
fs_date char(10) null,/*oͤ*/
kkyf char(10) null,/*ڤ*/
dj_date char(10) null,/*nO*/
memo char(100) null);/*ƪ`*/
go

create table gs_memo(
empid int not null,
datemonth char(7) null,
memo text null);
go
create table gz_information(
empid int null,
worknum char(6) not null,
jb char(3) null,
jsfs char(6) null,
jbgz float null,
jbxs float null,
zwjt float null,
nzjg float null,
zyjg float null,
ggjg float null,
jjjg float null,
quan float null,
chgdat char(10) null,
position char(10) null);
go
create table holiday_time(
ht_id int identity(1,1)  not null,
empid int null,
ht_name char(20)  null,
ht_date char(10) null,
begin_time int null,
end_time int null,
times real null,
memo char(60) null);
go
create table holiday_timeok(
ht_id int not null,
empid int null,
ht_name char(20)  null,
ht_date char(10) null,
begin_time int null,
end_time int null,
times real null,
memo char(60) null);
go

create table jj_emp(
empid int not null,
month char(7) not null,
fromdate char(10) null,
todate char(10) not null);
go
create table kq_data(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table linkclass(
lc_id int identity(1,1) not null,
name char(16) null,
depid int null,
ejdepid int null,
empid int null,
begin_time int null,
end_time int null,
memo char(60) null);
go
create table mytemp(
workday char(10) null,
one int null,
char1 char(10) null,
two int null,
char2 char(10) null,
three int null,
char3 char(10) null,
four int null,
char4 char(10) null,
five int null,
char5 char(10) null,
six int null,
char6 char(10) null,
seven int null,
char7 char(10) null,
eight int null,
char8 char(10) null,
nine int null,
char9 char(10) null,
ten int null,
char10 char(10) null);
go
create table other(
other_id int  identity(1,1)not null,
name char(16) null,
begin_time int null,
end_time int null,
memo char(60) null);
go

create table outwork(
outwork_id int identity(1,1) not null,
depid int null,
ejdepid int null,
empid int null,
begin_day int null,
begin_time int null,
end_day int null,
end_time int null,
memo char(60) null);
go
create table overdaykq_data(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table classname(
id int identity(1,1) not null,
name char(16) null)
go
create table overdaykq_data(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char(1) not null,
end_char(1) not null,
empid int null);
go

create table overdaykq_data_is_b(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table pass(
worknum char(6) not null,
password char(10) not null);
go
create table pbcatcol(
pbc_tnam char(129) not null,
pbc_tid int null,
pbc_ownr char (129) not null,
pbc_cnam char(129) not null,
pbc_cid smallint null,
pbc_labl varchar(254)  null,
pbc_lpos smallint null,
pbc_hdr varchar(254) null,
pbc_hpos smallint null,
pbc_jtfy smallint null,
pbc_mask varchar(3) null,
pbc_case smallint null,
pbc_hght smallint null,
pbc_wdth smallint null,
pbc_ptm varchar(31) null,
pbc_bmap char(1) null,
pbc_init varchar(254) null,
pbc_cmnt varchar(254) null,
pbc_edit varchar(31) null,
pbc_tag varchar(254) null);
go
create table pbcatedt(
pbe_name varchar(30) not null,
pbe_edit varchar(254) null,
pbe_type smallint null,
pbe_cntr int null,
pbe_seqn smallint not null,
pbe_flag int null,
pbe_work char(32) null);
go
create table pbcatfmt(
pbf_name varchar(30) not null,
pbf_frmt varchar(254) not null,
pbf_type smallint null,
pbf_cntr int null);
go
create table pbcattbl(
pbt_tnam char(129) not null,
pbt_tid int null,
pbt_ownr char (129) not null,
pbt_cnam char(129) not null,
pbt_cid smallint null,
pbt_labl varchar(254)  null,
pbt_lpos smallint null,
pbt_hdr varchar(254) null,
pbt_hpos smallint null,
pbt_jtfy smallint null,
pbt_mask varchar(3) null,
pbt_case smallint null,
pbt_hght smallint null,
pbt_wdth smallint null,
pbt_ptm varchar(31) null,
pbt_bmap char(1) null,
pbt_init varchar(254) null,
pbt_cmnt varchar(254) null,
pbt_edit varchar(31) null,
pbt_tag varchar(254) null);
go
create table pbcatvld(
pbv_name varchar(30) not null,
pbv_vald varchar(254) null,
pbv_type smallint null,
pbv_cntr int null,
pbv_msg varchar(254) null);
go
create table repeatedkq_data(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table result(
empid int null,
zb_time int null,
jb_time int null,
cd_time int null,
kg_time int null)
go
create table rsjbzl1(
no varchar(10) null,
worknum varchar(6) null,
cname varchar(8) null,
ename varchar(15) null,
sex bit null,
birthday datetime null,
idnum varchar(18) null,
telnum varchar(15) null,
homeadr varchar(50) null,
nowadr varchar(50) null,
indate datetime null,
bm varchar(10) null,
degree varchar(8) null,
position varchar(10) null,
jb varchar(3) null,
class float null,
posste float null,
chgdat datetime null,
holdat datetime null,
hfdat datetime null,
probadat float null,
lvldat datetime null,
basewage varchar(16) null,
posiwage varchar(3) null,
payscale varchar(3) null,
paytyp varchar(4) null,
jbgz float null,
jbxs float null,
zwjt float null,
jbbfjs float null,
acno varchar(15) null,
lvlturn varchar(1) null,
ability float null,
lvlret varchar(4) null,
gsdm varchar(6) null,
marry bit null,
gb varchar(10) null,
roomnum varchar(8) null,
jxfs varchar(1) null,
num float null,
jiguan varchar(10) null,
special varchar(40) null,
fang bit null,
huo bit null,
quan float null,
jtcy varchar(10) null,
gx varchar(10) null,
telphon varchar(14) null,
remark text null,
jsfs varchar(6) null,
nzjg float null,
zyjg float null,
jjjg float null,
ggjg float null,
kc float null,
flag varchar(1) null);
go
create table selectcardnumdate(
card_num char(10) null,
date_ymd char(10) null);
go
create table teams(
teams_id int identity(1,1) not null,
name char(16) null,
overday bit not null,
zbtime real null,
jbtime real null,
memo char(60) null);
go
create table teamtime(
time_id int identity (1,1) not null,
teamsid int not null,
name char(16) null,
begin_time int null,
begin_check char(1) null,
end_time int null,
end_check char(1) null,
memo char(60) null);
go

create table temp1_data(
data_id int identity(1,1) not null,
machine_num char(2) not null,
card_num char(10) not null,
date_y char(4) not null,
date_m char(2) not null,
date_d char(2) not null,
date_t char(1) not null,
check_char char(1) not null,
end_char char(1) not null);
go
create table temp2_data(
data_id int identity(1,1) not null,
machine_num char(2) not null,
card_num char(10) not null,
date_y char(4) not null,
date_m char(2) not null,
date_d char(2) not null,
date_t char(1) not null,
check_char char(1) not null,
end_char char(1) not null);
go
create table temprszl(
ejbm_id int null,
yjbm_name char(20) null,
ejbm_name char(20) null,
card_num char(10) null,
work_num char(10) null,
name char(10) null);
go
create table test_kqdata(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
check_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table timestable(
cardnum char(10) null,
workday datetime null,
moringup int null,
char1 char(1) null,
moringdown int null,
afternoonup int null,
char3 char(1) null,
afternoondown int null,
char4 char(1) null,
eveningup int null,
char5 char(1) null,
eveningdown int null,
char6 char(1) null,
exceptionclass char(16),
teamsid int null,
empid int null);
go
create table timestable_temp(
cardnum char(10) null,
workday datetime null,
moringup int null,
char1 char(1) null,
moringdown int null,
afternoonup int null,
char3 char(1) null,
afternoondown int null,
char4 char(1) null,
eveningup int null,
char5 char(1) null,
eveningdown int null,
char6 char(1) null,
exceptionclass char(16),
teamsid int null,
empid int null);
go
create table forgetpricard(
fpc_id int identity(1,1) not null,
depid int null,
ejdepid int null,
empid int null,
date_time datetime null,
times int nul,
memo char(60) null)
go
create table waitmat(
wait_id int identity(1,1) not null,
begin_day datetime null,
begin_time int null,
end_day datetime null,
end_time int null,
memo char(60) null);
go

create table y_data(
data_id int  not null,
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
date_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table y_dataok(
machine_num char(2) not null,
card_num char(10) not null,
date_ymd char(10) not null,
date_t char(4) not null,
date_char char(1) not null,
end_char char(1) not null,
empid int null);
go
create table yjbm(
yjbm_id int identity(1,1) not null,
yjbm_name char(10) not null);
go
/**/
create index ix_cardnum on bktimestable(card_num)
go
create index ix_checkchar on bktimestable(char1,char2,char3,char4,char5,char6)
go
create index ix_workday on bktimestable(workday)
go
create index ix_yjbm_id on ejbm(yjbm-id)
go
create index ix_ejbm_name on eubm(ejbm_name)
go
create index ix_dep_id on empdata(dep_id)
go
create index ix_work_num on empdata(work_num)
go
create index ix_card_num on empdata(card_num)
go
create index ix_name on empdata(name) 
go
create index ix_teamsid on empdata(teamsid)
go
create index ix_empid on employees(emp_id)
go
create index ix_date on y_data(date_ymd)
go
create index ix_y_data on y_data(card_num)
go
create index ix_empid on y_data(empid)
go
create index ix_empid on emptimestable (empid)
go
create index ix_cardnum on emptimestable(cardnum)
go
create index ix_workday on emptimestable(workday)
go
create index ix_empid on timestable(empid)
go
create index ix_workday on timestable(workday)
go

alter table changeteam
with nocheck
add constraint pk_changeteam primary key nonclustered(change_id)
go
alter table ejbm
with nocheck
add constraint pk_ejbm primary key nonclustered(ejbm_id)
go
alter talbe empdata
with nocheck
add constraint pk_empdata primary key nonclustered(ejbm_id)
constraint ck_card_num check(len(card_num)=10 or len(card_num)=0)
go
alter table employees
with nocheck
add constraint df_employees_sex default('k') for sex,
constraint df_employees_marry default('B') for marry
go
alter table forgetpricard
with nocheck
add constraint pk_forgetpricard primary key nonclustered(fpc_id)
go
alter table holiday_time
with nocheck
add constraint pk_holiday_time primary key nonclustered(ht_id)
go
alter table linkclass
with nocheck
add constraint pk_linkclass primary key nonclustered(lc_id)
go
alter table other
with nocheck
add constraint df_other_begin_time default('0') for begin_time,
constraint df_other_end_time default(24) for end_time,
constraint pk_other primary key nonclustered(other_id)
go
alter table outwork
with nocheck
add constraint df_outwork_begin_time default('0') for begin_time,
constraint df_outwork_end_time default ('24') for end_time
constraint pk_outwork primary key nonclustered(outwork_id)
go
alter table teams
with nocheck
add constraint df_teams_overday default('0') for overday,
constraint pk_set_teams primary key nonclustered(teams_id)
go
alter table teamstime
with nocheck
add constraint pk_set_teamstime primary key nonclustered(time_id)
go
alter table waitmat
with nocheck
add constraint pk_set teamstime primary key nonclustered(wait_id)
go
alter table yjbm
with nocheck
add constraint pk_yjbm primary key nonclustered(yjbm-id)




