createtable.txt

本系统是结合行业管理中实际的人力资源、工资制度

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BASE_DATA]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[BASE_DATA];

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CHECK_INFO]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[CHECK_INFO];

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WAGE_INFO]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[WAGE_INFO];

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[REWARD_PUN]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[REWARD_PUN];

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PRO_TITLE]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PRO_TITLE];

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WORK_AGE]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[WORK_AGE];

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USER_TABLE]') and 
OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[USER_TABLE];


CREATE TABLE [dbo].[BASE_DATA] (
	[WORKER_ID] [nvarchar]  (15)  NOT NULL ,
        [ARCHIVES_ID] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[WORKER_NAME] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[DEPARTMENT_NAME] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[SEX] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[BIRTHDAY] [datetime] NULL,
	[NATIVE_PLACE] [nvarchar]  (25)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [SCHOOL_AGE] [nvarchar]  (4)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [MAJOR] [nvarchar]  (15)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [GRADUATE_SCH] [nvarchar]  (10)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [POLITIC_STATUS] [nvarchar]  (5)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [WORK_TIME] [datetime] NULL,
        [COME_TIME] [datetime] NULL,
        [LINK_PHONE] [nvarchar]  (14)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [ADDRESS] [nvarchar]  (20)  COLLATE Chinese_PRC_CI_AS  NULL ,
        [PHOTO] [image] NULL ,
        [REMARK] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 

) ON [PRIMARY];

CREATE TABLE [dbo].[CHECK_INFO] (
	[WORKER_ID] [nvarchar] (15) not NULL ,
	[WORKER_NAME] [nvarchar](8)  COLLATE Chinese_PRC_CI_AS NULL ,
        [MONTHS] [nvarchar](10)  not NULL ,
        [MONTH_DAYS] [int]    NULL ,
	[HOLIDAYS] [int]    NULL ,
        [REAL_WORK_DAYS] [int]   NULL ,
        [WORK_DAYS] [int]   NULL ,
        [BUSINESS_DAYS] [int]   NULL ,
        [ABSENT_DAYS] [int]   NULL ,
        [LEAVE_TIMES] [int]   NULL ,
        [LATE_TIMES] [int]   NULL ,
        [LEAVE_EARLY_TIMES] [int]   NULL ,
        [HOLIDAY_OVERTIME] [int]   NULL ,
        [OTHER_OVERTIME] [int]   NULL ,
        [REMARK] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY];

CREATE TABLE [dbo].[WAGE_INFO] (
	[RECORD_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[WORKER_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[WORKER_NAME] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[BASE_WAGE] [float] NULL ,
        [ALLOWANCE1] [float] NULL ,
        [ALLOWANCE2] [float] NULL ,
        [REWARD] [float] NULL ,
        [OVERTIME_MONEY] [float] NULL ,
        [DEDUCT_CHECK] [float] NULL ,
        [AGED_MONEY] [float] NULL ,
        [MEDICAL_MONEY] [float] NULL ,
        [COMPO_MONEY] [float] NULL ,
        [UNEMPLOY_MONEY] [float] NULL ,
        [HOUSING_MONEY] [float] NULL ,
        [INCOME_TAX] [float] NULL ,
        [REAL_WAGE] [float] NULL ,
        [GIVE_DATE] [nvarchar] (6) COLLATE Chinese_PRC_CI_AS NULL        
) ON [PRIMARY];

CREATE TABLE [dbo].[REWARD_PUN] (
        [RECORD_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[WORKER_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[WORKER_NAME] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
        [RECORD_DATE] [datetime] NULL, 
        [RECORD_TYPE] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
	[RECORD_CONTENT] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [RECORD_REASON] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [ALLOW_UNIT] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[REMARK] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY];


CREATE TABLE [dbo].[PRO_TITLE] (
        [WORKER_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[WORKER_NAME] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
        [ASSESS_DATE] [datetime] NULL,
        [ASSESS_UNIT] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[ASSESS_TITLE] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
	[REMARK] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY];


CREATE TABLE [dbo].[WORK_AGE] (
        [RECORD_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[WORKER_ID] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
	[WORKER_NAME] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
        [BEGIN_DATE] [datetime] NULL,
        [OVER_DATE] [datetime] NULL,
        [UNIT_NAME] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
        [WORK_TIME] [float] NULL ,
        [DUTY] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
        [LEVEL1] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL ,
	[REMARK] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY];

CREATE TABLE [dbo].[USER_TABLE] (
	[USER_NAME] [nvarchar] (30) not NULL ,
	[USER_PWD] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
        [POWER_LEVEL] [nvarchar] (8) COLLATE Chinese_PRC_CI_AS NULL 
) ON [PRIMARY];


INSERT INTO  USER_TABLE(USER_NAME,USER_PWD,POWER_LEVEL) VALUES 
('system','system','系统管理员');