SQL Function示例程序01

/****** Object:  UserDefinedFunction [dbo].[GetActualBaseSalary]    Script Date: 02/01/2012 10:52:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--获取员工实际底薪
--@oid  促销员号
--@caculate_end_date 计薪截止日期
--@caculate_end_date 计薪截止日期
ALTER FUNCTION [dbo].[GetActualBaseSalary]
(
 @oid                 varchar(50),
 @caculate_begin_date varchar(10),
 @caculate_end_date   varchar(10)
)
RETURNS decimal(18,2)
AS
BEGIN
declare @FULL_TIME_JOB_DAYS          int 
declare @NULL_DATETIME               varchar(100)
declare @caculate_salary_begin_date  datetime
declare @caculate_salary_end_date    datetime
declare @actual_base_salary          decimal(18,2)
declare @actual_job_days             int
declare @join_time                   datetime
declare @leave_time                  datetime
declare @before_leave                int          --是否提前离职
declare @after_join                  int          --是否之后入职  

SET @NULL_DATETIME = '1900-01-01'
SET @FULL_TIME_JOB_DAYS=30     --全勤天数30
SET @actual_job_days = 0       --实际上班天数
SET @actual_base_salary = 0
SET @caculate_salary_begin_date = CAST(@caculate_begin_date AS DATETIME)
SET @caculate_salary_end_date = CAST(@caculate_end_date AS DATETIME)
SET @before_leave = 0
SET @after_join = 0

--获取月全勤天数，统一按照参数进行计算
SELECT @FULL_TIME_JOB_DAYS = CAST(dbo.[GetParaValueByCode]('HRXZ_MONTH_FULL_DAYS') AS INT)

--实际底薪在正常情况下等于标准底薪
SELECT @actual_base_salary=AFTER_ADJUST_SALARY,@join_time=JoinTime,@leave_time=LeaveTime from HR_Promoter WHERE oid=@oid
IF @actual_base_salary<>0 
	BEGIN 
		--在促销员入职或者离职月份则有差异。
		--如果该促销员入职日期到计薪截止日期满30天则给全薪，如果未满30天，则等于（标准底薪/30）* （计薪截止日期-入职日期，即实际上班天数）；
		--如果该促销员离职日期到计薪开始日期满30天则给全薪，如果未满30天，则等于（标准底薪/30）* （离职日期-计薪开始日期，即实际上班天数）。

		--判断该员工是否离职
		IF CONVERT(varchar(100),@leave_time,23)<>@NULL_DATETIME
			--离职
			BEGIN
				--如果离职日期小于计薪结束日期，计算薪资到离职日期为止
				IF DATEDIFF(day,@caculate_salary_end_date,@leave_time)<0	
					BEGIN		
						SET @caculate_salary_end_date = @leave_time
						SET @before_leave = 1
					END

				--从薪资计算开始日期到计薪结束日期之间上班的天数
				SET @actual_job_days = datediff(day,@caculate_salary_begin_date,dateadd(day,1,@caculate_salary_end_date))  --截止日期当天也需要算薪资		
				IF @actual_job_days>0
					BEGIN
						--满30天给全薪，不满30天，则通过计算
						IF @actual_job_days<@FULL_TIME_JOB_DAYS AND @before_leave=1
							SET @actual_base_salary = @actual_base_salary * @actual_job_days / @FULL_TIME_JOB_DAYS	
					END
				ELSE
					SET @actual_base_salary = 0
			END 	
		ELSE
			--未离职
			BEGIN				
				IF CONVERT(varchar(100),@join_time,23)=@NULL_DATETIME
					SET @actual_base_salary = 0  --未入职
				ELSE
					BEGIN
						--计薪开始日期如果小于入职日期，薪资从入职日期开始算天数
						IF DATEDIFF(day,@caculate_salary_begin_date,@join_time)>0	
							BEGIN
								SET @caculate_salary_begin_date = @join_time					
								SET @after_join = 1
							END

						SET @actual_job_days = datediff(day,@caculate_salary_begin_date,dateadd(day,1,@caculate_salary_end_date))
						IF @actual_job_days>0
							BEGIN
								--入职满30天全薪，不满30天，则通过计算
								IF @actual_job_days<@FULL_TIME_JOB_DAYS AND @after_join=1
									SET @actual_base_salary = @actual_base_salary * @actual_job_days / @FULL_TIME_JOB_DAYS	
							END
						ELSE
							SET @actual_base_salary = 0
					END
			END
	END


RETURN @actual_base_salary
END

/****** Object:  UserDefinedFunction [dbo].[GetAdjustSalary]    Script Date: 02/01/2012 10:53:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--获取员工调整工资
--@oid  工号ID
--@caculate_begin_date 计薪开始日期
--@caculate_end_date 计薪截止日期 
ALTER FUNCTION [dbo].[GetAdjustSalary]
(
 @oid                 nvarchar(50),
 @caculate_begin_date nvarchar(10),
 @caculate_end_date   nvarchar(10)
)
RETURNS decimal(18,2)
AS
BEGIN
	declare @type_salary			  decimal(18,2)
	declare @caculate_begin_datetime  datetime
	declare @caculate_end_datetime    datetime
	declare @IS_ACTIVE				  INT
	declare @staffnumber			  nvarchar(20)	

	SET @IS_ACTIVE = 1
	SET @type_salary = 0
	SET @caculate_begin_datetime = CAST(@caculate_begin_date AS DATETIME)
	SET @caculate_end_datetime = CAST(@caculate_end_date AS DATETIME)

	SELECT @staffnumber=STAFFNUMBER FROM HR_Promoter WHERE oid=@oid
	IF @staffnumber IS NULL
		SET  @type_salary = 0	
	ELSE
	BEGIN 
		SELECT @type_salary=isnull(SUM(S_ADJUST),0) FROM HRXZ_SALARY_OTHER_ITEMS WHERE 
			PTR_NO=@staffnumber AND IS_ACTIVE=@IS_ACTIVE
			AND EFFECT_DATE >=@caculate_begin_datetime AND EFFECT_DATE<= @caculate_end_datetime
	END
	IF @type_salary IS NULL
	BEGIN		
		SET  @type_salary = 0 	
	END
	RETURN @type_salary
END

ALTER   function [dbo].[f_GetChildOID](@POID varchar(40))
RETURNS @TEMP TABLE(OID VARCHAR(40))
AS
BEGIN
INSERT INTO  @TEMP (oid) values (@POID) 
 
WHILE @@ROWCOUNT>0
 INSERT @TEMP 
  SELECT A.OID 
  FROM TBL_SALEORG A INNER JOIN @TEMP B ON A.PARENTOID=B.OID 
  WHERE A.OID NOT IN(SELECT OID FROM @TEMP) AND SOG_STATUS='usage'  
RETURN
END