/****** 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