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

使用的人力资源管理系统

GO

-- 外键关联字段
ALTER TABLE [dbo].[月工资统计表] ADD 
	CONSTRAINT [FK_月工资统计表_职员基本信息表] FOREIGN KEY 
	(
		[职员编号]
	) REFERENCES [dbo].[职员基本信息表] (
		[职员编号]
	)
GO

ALTER TABLE [dbo].[工资发放历史表] ADD 
	CONSTRAINT [FK_工资发放历史表_职员基本信息表] FOREIGN KEY 
	(
		[职员编号]
	) REFERENCES [dbo].[职员基本信息表] (
		[职员编号]
	)
GO

ALTER TABLE [dbo].[职员奖励表] ADD 
	CONSTRAINT [FK_职员奖励表_职员基本信息表] FOREIGN KEY 
	(
		[职员编号]
	) REFERENCES [dbo].[职员基本信息表] (
		[职员编号]
	)
GO

ALTER TABLE [dbo].[职员惩罚表] ADD 
	CONSTRAINT [FK_职员惩罚表_职员基本信息表] FOREIGN KEY 
	(
		[职员编号]
	) REFERENCES [dbo].[职员基本信息表] (
		[职员编号]
	)
GO

-- 存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_空数据置0]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_空数据置0]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_形成月工资统计表]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_形成月工资统计表]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_当月工资统计]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_当月工资统计]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sf_当月工资发放]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sf_当月工资发放]
GO
-----------------------
create procedure sf_空数据置0
as
begin transaction
	update 月工资统计表 set 基本工资=0 where 基本工资 is null
	update 月工资统计表 set 职务工资=0 where 职务工资 is null
	update 月工资统计表 set 工龄工资=0 where 工龄工资 is null
	update 月工资统计表 set 考核工资=0 where 考核工资 is null
	update 月工资统计表 set 奖金=0 where 奖金 is null
	update 月工资统计表 set 应发金额合计=0 where 应发金额合计 is null
	update 月工资统计表 set 房租=0 where 房租 is null
	update 月工资统计表 set 水电费=0 where 水电费 is null
	update 月工资统计表 set 请假扣除=0 where 请假扣除 is null
	update 月工资统计表 set 罚款=0 where 罚款 is null
	update 月工资统计表 set 考勤扣除=0 where 考勤扣除 is null
	update 月工资统计表 set 住房公积金=0 where 住房公积金 is null
	update 月工资统计表 set 医疗保险=0 where 医疗保险 is null
	update 月工资统计表 set 养老保险=0 where 养老保险 is null
	update 月工资统计表 set 应扣金额合计=0 where 应扣金额合计 is null
	update 月工资统计表 set 失业保险=0 where 失业保险 is null
	update 月工资统计表 set 个人所得税=0 where 个人所得税 is null
	update 月工资统计表 set 工资合计=0 where 工资合计 is null
	update 月工资统计表 set 实发金额=0 where 实发金额 is null
	update 个人所得税表 set 个人所得税率=0 where 个人所得税率 is null
commit
GO
-------------------------------------
create procedure sf_当月工资统计
as
begin transaction
	declare @month int
	select @month = 日期 from 月工资统计表
	-- 计算奖金
	update 月工资统计表 set 奖金 = 
		(select sum(奖励金额) from 职员奖励表 as i where i.是否计入工资='是' and 
		i.奖励日期>@month*100 and i.奖励日期<(@month+1)*100 
		group by i.职员编号 having i.职员编号=月工资统计表.职员编号)
	update 月工资统计表 set 奖金=0 where 奖金 is null
	-- 计算惩罚
	update 月工资统计表 set 罚款 = 
		(select sum(惩罚金额) from 职员惩罚表 as i where i.是否计入工资='是' and 
		i.惩罚日期>@month*100 and i.惩罚日期<(@month+1)*100 
		group by i.职员编号 having i.职员编号=月工资统计表.职员编号)
	update 月工资统计表 set 罚款=0 where 罚款 is null
	-- 将空数据置0
	exec sf_空数据置0
	-- 计算应发/应扣/工资合计
	update 月工资统计表
		set 应扣金额合计 = 房租+水电费+请假扣除+罚款+考勤扣除+住房公积金+医疗保险
			+养老保险+失业保险
	update 月工资统计表
		set 应发金额合计=基本工资+浮动工资+合同补+粮副补+房补+临时补
			+职务工资+工龄工资+ 考核工资+ 奖金
	update 月工资统计表
		set 工资合计 = 应发金额合计 - 应扣金额合计
	-- 计算个人所得税
	declare @gzhj numeric(20,4), @zybh varchar(20), @grsds numeric(20,4),@sl float, 
		@gzxx numeric(20,4),@gzsx numeric(20,4)
	declare mycur cursor for select 职员编号,工资合计 from 月工资统计表
	open mycur
	fetch next from mycur into @zybh,@gzhj
	WHILE (@@FETCH_STATUS = 0 )
	BEGIN
		-- 扣除不计税部分
		select @gzhj = @gzhj - (select top 1 不计税工资 from 个人所得税表 order by 级数)
		select @grsds = 0
		declare mycur1 cursor for select 工资下限,工资上限,case when 
			个人所得税率>1 then 个人所得税率/100 else 个人所得税率 end 
			from 个人所得税表 where 工资上限 <= @gzhj order by 工资上限  
		open mycur1
		fetch next from mycur1 into @gzxx, @gzsx,@sl
		WHILE (@@FETCH_STATUS = 0 )
		BEGIN
			select @grsds = @grsds + (@gzsx-@gzxx)*@sl
			fetch next from mycur1 into @gzxx, @gzsx,@sl
		END
		close mycur1
		deallocate mycur1
		select @sl = case when 个人所得税率>1 then 个人所得税率/100 else 
			个人所得税率 end , @gzxx = 工资下限 from 个人所得税表 
			where 工资下限=@gzsx
		select @grsds = @grsds + (@gzhj-@gzxx)*@sl

		update 月工资统计表 set 个人所得税 = @grsds where current of mycur
		fetch next from mycur into @zybh,@gzhj
	END
	close mycur
	deallocate mycur
	-- 计算实发工资
	update 月工资统计表 set 实发金额 = 工资合计-个人所得税
commit
GO
-------------------------------------
create procedure sf_形成月工资统计表 @导入工资月份 int, @统计工资月份 int
as
begin transaction
	delete from 月工资统计表    -- 删除已有数据
	if exists (select * from 工资发放历史表 where 日期=@导入工资月份)
	begin
		-- 从历史倒入
		insert into 月工资统计表
			(日期, 职员编号, 基本工资, 浮动工资, 合同补, 粮副补, 房补, 临时补, 
			职务工资, 工龄工资, 考核工资, 奖金, 应发金额合计, 房租, 水电费,
			请假扣除, 考勤扣除, 罚款, 住房公积金, 医疗保险, 养老保险, 失业保险, 
			生育保险, 工伤保险, 应扣金额合计, 工资合计, 个人所得税, 实发金额,
			发放否, 月份)
			select @统计工资月份, 职员编号, 基本工资, 浮动工资, 合同补, 粮副补, 
			房补, 临时补, 职务工资, 工龄工资, 考核工资, 奖金, 
			应发金额合计, 房租, 水电费, 请假扣除, 考勤扣除, 罚款,
			住房公积金, 医疗保险, 养老保险, 失业保险, 生育保险, 
			工伤保险, 应扣金额合计, 工资合计, 个人所得税, 实发金额,
			发放否, 月份
			from 工资发放历史表 as h where h.日期=@导入工资月份 
	end
	-- 新的员工,历史没有记录,生成记录
	insert into 月工资统计表	(职员编号,日期) select 职员编号,@统计工资月份 
		from 职员基本信息表 as h where h.职员编号 not in 
		(select 职员编号 from  月工资统计表)
	-- 将空数据置0
	exec sf_空数据置0
	-- 自动计算工资
	exec sf_当月工资统计
commit
GO
--------------------- 发放指定职员的工资
create procedure sf_当月工资发放 @职员编号 char(10)
as
begin transaction
	update 月工资统计表 set 发放否='是' from 月工资统计表 
		where 职员编号=@职员编号
	insert into 工资发放历史表
		select * from 月工资统计表 
		where 职员编号=@职员编号
	delete 月工资统计表 where 职员编号=@职员编号
commit
GO

--- 初始化数据
insert into 用户清单 values('1','系统管理','sys','M','sys')
insert into 用户清单 values('zs','采购部','张三','M','sys')
insert into 用户清单 values('ls','销售部','李四','M','sys')
insert into 用户清单 values('ww','财务部','王五','M','sys')
go