最近遇到一个业务。客户要求维护一个请假表，包含员工信息，请假信息！每年1月1日需要给员工增加新一年的年假，但3月才清除去年未修的年假！每次员工请假审批成功后需要同步更新假期表。
增加年假
USE [FE_APP5]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Annual_leave]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Annual_leave]
GO
USE [FE_APP5]
GO
/****** Object: StoredProcedure [dbo].[Annual_leave] Script Date: 11/07/2017 18:10:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[Annual_leave]
as
declare @FN01 varchar(20) -- 员工号
declare @FN07 int -- 剩余年假
declare @FN05 int -- 年假天数
declare @FN04 date -- 入职时间
declare @day int = 5 -- 默认年假天数
declare @currentyear int
declare @year int
declare my_cur cursor for select FN01,FN05,FN07,FN04 from FT_1_49E74BA98FF7886574E1
open my_cur
fetch next from my_cur into @FN01,@FN05,@FN07,@FN04
while @@FETCH_STATUS = 0
begin
--print @FN01
--print @FN05
--print @FN07
-- 当年假期计算规则
if MONTH(@FN04) < 7
set @currentyear = YEAR(getdate()) - YEAR(@FN04) + @day
else if MONTH(@FN04) >= 7
set @currentyear = YEAR(getdate()) - YEAR(@FN04) + @day - 1
print @currentyear
if @currentyear > 12
set @currentyear = 12
set @year = @currentyear + @FN07 -- 当前总年假
--print @year
UPDATE FT_1_49E74BA98FF7886574E1 SET FN05 = @year,FN07 = @year,FN06 = 0,FN08 = @FN07 where FN01 = @FN01;
fetch next from my_cur into @FN01,@FN05,@FN07,@FN04
end
close my_cur
deallocate my_cur
--select * from FT_1_49E74BA98FF7886574E1
--exec Annual_leave
GO以上使用了游标！
清空年假
USE [FE_APP5]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[[Annual_leave_clean]]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Annual_leave_clean]
GO
USE [FE_APP5]
GO
/****** Object: StoredProcedure [dbo].[Annual_leave] Script Date: 11/07/2017 18:10:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[Annual_leave_clean]
as
declare @FN01 varchar(20) -- 员工号
declare @FN07 int -- 剩余年假
declare @FN05 int -- 年假天数
declare @FN04 date
declare @day int = 5 -- 默认年假天数
declare @currentyear int
declare @year int
declare my_cur cursor for select FN01,FN05,FN07,FN04 from FT_1_49E74BA98FF7886574E1
open my_cur
fetch next from my_cur into @FN01,@FN05,@FN07,@FN04
while @@FETCH_STATUS = 0
begin
--print @FN01
--print @FN05
--print @FN07
--UPDATE FT_1_49E74BA98FF7886574E1 SET FN05 = @year,FN07 = @year,FN06 = 0,FN08 = @FN07 where FN01 = @FN01;
update FT_1_49E74BA98FF7886574E1 set FN08 = 0;
fetch next from my_cur into @FN01,@FN05,@FN07,@FN04
end
close my_cur
deallocate my_cur
--select * from FT_1_49E74BA98FF7886574E1
--exec Annual_leave
GO触发
USE [FE_APP5]
GO
/****** Object: Trigger [tgr_Annual_leave_update] Script Date: 11/08/2017 11:54:59 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tgr_Annual_leave_update]'))
DROP TRIGGER [dbo].[tgr_Annual_leave_update]
GO
USE [FE_APP5]
GO
/****** Object: Trigger [dbo].[tgr_Annual_leave_update] Script Date: 11/08/2017 11:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [dbo].[tgr_Annual_leave_update]
on [dbo].[FT_1_64F2780EF737209A290C]
after update
as
-- FN01 申请人
-- FN02 员工号
-- FN15 假期类型
-- FN19 请假天数
-- SPFLAG 送审标识
declare @FN01 varchar(50),@FN02 varchar(50),@FN15 varchar(50),@FN19 int,@SPFLAG int
select @SPFLAG = SPFLAG,@FN01 = FN01,@FN02 = FN02, @FN15 = FN15,@FN19 = FN19,@SPFLAG = SPFLAG from inserted
--print @FN01
--print @FN02
--print @FN15
--print @FN19
--print @SPFLAG
if @SPFLAG = 2 and @FN15 = '年假'
update FT_1_49E74BA98FF7886574E1 set FN07 = FN07-@FN19,FN06 = FN06+@FN19 where FN01 = @FN02
--select * from FT_1_49E74BA98FF7886574E1
--select * from FT_1_64F2780EF737209A290C
--update FT_1_49E74BA98FF7886574E1 set SPFLAG =2 where FN01 = 'AC0001'
GO
文章评论