hr.sql

一个完整的人事管理信息系统

USE master
GO
CREATE DATABASE HR
GO

USE HR
GO
CREATE TABLE Checkin
  (CheckDate	char(10) NOT NULL,
   Emp_Id 	int NOT NULL,
   qqDays    	decimal(4,1),
   ccDays	decimal(4,1),
   bjDays	decimal(4,1),
   sjDays	decimal(4,1),
   kgDays	decimal(4,1),
   fdxjDays	decimal(4,1),
   nxjDays	decimal(4,1),
   dxjDays	decimal(4,1),
   cdMinutes	tinyint,
   ztMinutes	tinyint,
   ot1Days	decimal(4,1),
   ot2Days	decimal(4,1),
   ot3Days 	decimal(4,1),
   Memo	varchar(200)
  )
GO

USE HR
GO
CREATE TABLE Departments
  (Dep_id       	int PRIMARY KEY IDENTITY,
   Dep_name	varchar(40) NOT NULL,
   Describe 	varchar(400),
   UpperId	int NOT NULL
  )
GO

USE HR
GO
CREATE TABLE Employees
  (Emp_Id	int PRIMARY KEY IDENTITY,
   Emp_NAME    	varchar(50) NOT NULL,
   Photo	image,
   Sex	char(2),
   Nationality	varchar(40),
   Birth	varchar(20),
   Political_Party	varchar(40),
   Culture_Level	varchar(40),
   Marital_Condition	varchar(20),
   Family_Place	varchar(60),
   Id_Card	varchar(20),
   BadgeID	varchar(40),
   Office_phone	varchar(30),
   Mobile	varchar(30),
   Files_Keep_Org	varchar(100),
   Hukou	varchar(100),
   HireDate	varchar(20),
   Dep_Id	int,
   Position	varchar(40),
   Title	varchar(20),
   State	tinyint,
   UpperId	int,
   Contract_Duration	varchar(20),
   Memo	varchar(200),
   Fillin_Person	varchar(30),
   Fillin_Time	varchar(20)
  )
GO


USE HR
GO
CREATE TABLE Evaluation
  (EvaMonth	char(10) NOT NULL,
   Emp_Id 	int NOT NULL,
   ztEva	varchar(200),
   jlReason    	varchar(200),
   jlAmount	smallint,
   cfReason	varchar(200),
   cfAmount	smallint,
   Memo	varchar(200)
  )
GO


USE HR
GO
CREATE TABLE Experience
  (Id	tinyint PRIMARY KEY IDENTITY,
   Emp_Id 	int NOT NULL,
   Start_Date 	char(10),
   End_Date    	char(10),
   School_Org	varchar(50),
   Title	varchar(20)
  )
GO

USE HR
GO
CREATE TABLE Family
  (Id	tinyint PRIMARY KEY IDENTITY,
   Emp_Id 	int NOT NULL,
   Name	varchar(50) NOT NULL,
   Sex    	char(2),
   Age	tinyint,
   Relationship	varchar(20),
   WorkingOrg	varchar(40)
  )
GO

USE HR
GO
CREATE TABLE Users
  (UserName  		varchar(40) PRIMARY KEY,
   Pwd		varchar(40) NOT NULL,
   User_Type  		tinyint NOT NULL
  )
GO
INSERT INTO Users Values('Administrator', '888888', 1)
GO

USE HR
GO
CREATE VIEW dbo.v_SumCheck
AS
SELECT LEFT(dbo.Checkin.CheckDate, 7) AS CheckMonth, dbo.Employees.Emp_NAME, 
      SUM(dbo.Checkin.qqDays) AS Sumqq, SUM(dbo.Checkin.ccDays) AS SumCc, 
      SUM(dbo.Checkin.bjDays) AS SumBj, SUM(dbo.Checkin.sjDays) AS SumSj, 
      SUM(dbo.Checkin.kgDays) AS SumKg, SUM(dbo.Checkin.fdxjDays) AS SumFdxj, 
      SUM(dbo.Checkin.nxjDays) AS SumNxj, SUM(dbo.Checkin.dxjDays) AS SumDxj, 
      SUM(dbo.Checkin.cdMinutes) AS SumCd, SUM(dbo.Checkin.ztMinutes) AS SumZt, 
      SUM(dbo.Checkin.ot1Days) AS SumOt1, SUM(dbo.Checkin.ot2Days) AS SumOt2, 
      SUM(dbo.Checkin.ot3Days) AS SumOt3, dbo.Employees.Dep_Id
FROM dbo.Checkin INNER JOIN
      dbo.Employees ON dbo.Checkin.Emp_Id = dbo.Employees.Emp_Id
GROUP BY LEFT(dbo.Checkin.CheckDate, 7), dbo.Employees.Emp_NAME, 
      dbo.Checkin.Emp_Id, dbo.Employees.Dep_Id