人力资源管理实例程序.sql

进销存管理实例程序源代码

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_工资发放历史表_职员基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[工资发放历史表] DROP CONSTRAINT FK_工资发放历史表_职员基本信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_月工资统计表_职员基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[月工资统计表] DROP CONSTRAINT FK_月工资统计表_职员基本信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_职员惩罚表_职员基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[职员惩罚表] DROP CONSTRAINT FK_职员惩罚表_职员基本信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_职员奖励表_职员基本信息表]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[职员奖励表] DROP CONSTRAINT FK_职员奖励表_职员基本信息表
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[用户清单]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[用户清单]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[组织机构编码表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[组织机构编码表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[职员基本信息表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[职员基本信息表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[月工资统计表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[月工资统计表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[个人所得税表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[个人所得税表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[工资发放历史表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[工资发放历史表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[职员奖励表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[职员奖励表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[职员惩罚表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[职员惩罚表]
GO

CREATE TABLE [dbo].[用户清单] (
	[用户编号] [char] (6) NOT NULL ,
	[部门] [char] (20) NOT NULL ,
	[姓名] [char] (10) NOT NULL ,
	[性别] [char] (2) NOT NULL ,
	[密码] [char] (10) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[组织机构编码表] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[类别] [varchar] (100) NULL ,
	[AbsIndex] [int] NULL ,
	[ItemIndex] [int] NULL ,
	[ItemLevel] [int] NULL ,
	[ParentIndex] [int] NULL ,
	[类别号] [char] (10) NULL ,
	[单位编号] [varchar] (20) NOT NULL PRIMARY KEY,
	[单位名称] [varchar] (100) NULL ,
	[拼音编码] [varchar] (50) NULL ,
	[单位地址] [varchar] (100) NULL ,
	[单位电话号码] [varchar] (50) NULL ,
	[开户银行] [varchar] (100) NULL ,
	[帐号] [varchar] (50) NULL ,
	[开户全称] [varchar] (100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[职员基本信息表] (
	[内部编号] [int] IDENTITY (1, 1) NOT NULL ,
	[职员编号] [varchar] (30) NOT NULL PRIMARY KEY,
	[姓名] [varchar] (20) NOT NULL ,
	[姓名简码] [varchar] (10) NULL ,
	[性别] [varchar] (2) NULL ,
	[出生日期] [int] NULL ,
	[年龄] [int] NULL ,
	[籍贯] [varchar] (50) NULL ,
	[民族] [varchar] (20) NULL ,
	[文化程度] [char] (50) NULL ,
	[毕业学校] [char] (100) NULL ,
	[健康状况] [varchar] (50) NULL ,
	[婚姻状况] [varchar] (10) NULL ,
	[身份证号码] [varchar] (18) NULL ,
	[家庭电话] [varchar] (50) NULL ,
	[办公电话] [varchar] (50) NULL ,
	[手机] [varchar] (50) NULL ,
	[电子邮件地址] [varchar] (50) NULL ,
	[职工账号] [varchar] (20) NULL ,
	[单位编号] [varchar] (20) NULL, 
	[备注] [varchar] (100) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[月工资统计表] (
	[日期] [int] NULL ,
	[职员编号] [varchar] (30) Not NULL PRIMARY KEY,
	[基本工资] [float] NULL DEFAULT (0) ,
	[浮动工资] [decimal](26, 2) NULL DEFAULT (0) ,
	[合同补] [decimal](26, 2) NULL DEFAULT (0) ,
	[粮副补] [decimal](26, 2) NULL DEFAULT (0) ,
	[房补] [decimal](26, 2) NULL DEFAULT (0) ,
	[临时补] [decimal](26, 2) NULL DEFAULT (0) ,
	[职务工资] [decimal](26, 2) NULL DEFAULT (0) ,
	[工龄工资] [decimal](26, 2) NULL DEFAULT (0) ,
	[考核工资] [decimal](26, 2) NULL DEFAULT (0) ,
	[奖金] [decimal](26, 2) NULL DEFAULT (0) ,
	[应发金额合计] [decimal](26, 2) NULL DEFAULT (0) ,
	[房租] [decimal](26, 2) NULL DEFAULT (0) ,
	[水电费] [decimal](26, 2) NULL DEFAULT (0) ,
	[请假扣除] [decimal](26, 2) NULL DEFAULT (0) ,
	[考勤扣除] [decimal](26, 2) NULL DEFAULT (0) ,
	[罚款] [decimal](26, 2) NULL DEFAULT (0) ,
	[住房公积金] [decimal](26, 2) NULL DEFAULT (0) ,
	[医疗保险] [decimal](26, 2) NULL DEFAULT (0) ,
	[养老保险] [decimal](26, 2) NULL DEFAULT (0) ,
	[失业保险] [decimal](26, 2) NULL DEFAULT (0) ,
	[生育保险] [decimal](26, 2) NULL DEFAULT (0) ,
	[工伤保险] [decimal](26, 2) NULL DEFAULT (0) ,
	[应扣金额合计] [decimal](26, 2) NULL DEFAULT (0) ,
	[工资合计] [decimal](26, 2) NULL DEFAULT (0) ,
	[个人所得税] [decimal](26, 2) NULL DEFAULT (0) ,
	[实发金额] [decimal](26, 2) NULL DEFAULT (0) ,
	[发放否] [char] (10) NULL ,
	[月份] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[个人所得税表] (
	[编号] [int] NOT NULL PRIMARY KEY,
	[级数] [char] (2) NOT NULL ,
	[不计税工资] [decimal](26, 2) NOT NULL DEFAULT (800),
	[工资下限] [decimal](26, 2) NOT NULL DEFAULT (0),
	[工资上限] [decimal](26, 2) NOT NULL DEFAULT (0),
	[个人所得税率] [decimal](26, 2) NOT NULL DEFAULT (0),
	[速算扣除数] [decimal](26, 2) NOT NULL DEFAULT (0),
	[备注] [varchar] (50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[工资发放历史表] (
	[日期] [int] NULL ,
	[职员编号] [varchar] (30) NULL ,
	[基本工资] [float] NULL ,
	[浮动工资] [decimal](26, 2) NULL ,
	[合同补] [decimal](26, 2) NULL ,
	[粮副补] [decimal](26, 2) NULL ,
	[房补] [decimal](26, 2) NULL ,
	[临时补] [decimal](26, 2) NULL ,
	[职务工资] [decimal](26, 2) NULL ,
	[工龄工资] [decimal](26, 2) NULL ,
	[考核工资] [decimal](26, 2) NULL ,
	[奖金] [decimal](26, 2) NULL ,
	[应发金额合计] [decimal](26, 2) NULL ,
	[房租] [decimal](26, 2) NULL ,
	[水电费] [decimal](26, 2) NULL ,
	[请假扣除] [decimal](26, 2) NULL ,
	[考勤扣除] [decimal](26, 2) NULL ,
	[罚款] [decimal](26, 2) NULL ,
	[住房公积金] [decimal](26, 2) NULL ,
	[医疗保险] [decimal](26, 2) NULL ,
	[养老保险] [decimal](26, 2) NULL ,
	[失业保险] [decimal](26, 2) NULL ,
	[生育保险] [decimal](26, 2) NULL ,
	[工伤保险] [decimal](26, 2) NULL ,
	[应扣金额合计] [decimal](26, 2) NULL ,
	[工资合计] [decimal](26, 2) NULL ,
	[个人所得税] [decimal](26, 2) NULL ,
	[实发金额] [decimal](26, 2) NULL ,
	[发放否] [char] (10) NULL ,
	[月份] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[职员奖励表] (
	[序号] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[职员编号] [varchar] (30) NOT NULL ,
	[奖励类型] [varchar] (20) NULL ,
	[奖励金额] [float] NULL DEFAULT (0),
	[是否计入工资] [char] (10) NULL DEFAULT ('否'),
	[奖励原因] [varchar] (50) NULL ,
	[部门意见] [varchar] (100) NULL ,
	[奖励日期] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[职员惩罚表] (
	[序号] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	[职员编号] [varchar] (30) NOT NULL ,
	[惩罚类型] [varchar] (20) NULL ,
	[惩罚金额] [float] NULL DEFAULT (0) ,
	[是否计入工资] [char] (10) NULL DEFAULT ('否'),
	[惩罚原因] [varchar] (50) NULL ,
	[部门意见] [varchar] (100) NULL ,
	[惩罚日期] [int] NULL
) ON [PRIMARY]