CREATE FUNCTION dbo.CFN_salarymonth_2
(
@badge VARCHAR(20) ,--人员
@term SMALLDATETIME ,--薪资月份
@amount1 DECIMAL(18, 4) , --春夏秋目标
@amount2 DECIMAL(18, 4) , --冬装目标
@type INT ,--类型
@type2 INT --提成范围
)
RETURNS DECIMAL(18, 4)
BEGIN
DECLARE @eid INT ,
@num INT , --店铺营业员人数
@jobtype INT ,
@SHOP_MANAGER INT ,--店长
@GROUPLEADER INT ,--组长
@value DECIMAL(18, 4) ,
@value31 DECIMAL(18, 4) ,
@value32 DECIMAL(18, 4) ,
@value33 DECIMAL(18, 4) ,
@value34 DECIMAL(18, 4) ,
@value35 DECIMAL(18, 4) ,
@value36 DECIMAL(18, 4) ,
@value37 DECIMAL(18, 4) ,
@value38 DECIMAL(18, 4);
SELECT @eid = EID
FROM dbo.eEmployee
WHERE Badge = @badge;
SELECT @jobtype = jobtype, @SHOP_MANAGER = shop_manager, @GROUPLEADER = groupleader
FROM dbo.eleader_sales
WHERE eid = @eid;
--计算人数时店铺所有人
SELECT @num = COUNT(a.eid)
FROM dbo.eleader_sales a
WHERE a.depid IN ( SELECT b.depid
FROM dbo.eleader_sales b
WHERE b.eid = @eid )
;
if (@num=0)
begin
return @num
end
--@type 31 春夏秋结算金额 32 春夏秋实销金额 35 冬装结算金额 36冬装实销金额
-- 6 春夏秋完成率 7 冬装完成率
-- CCOMMISSION1_X2
--9 春夏秋完成率≤120%金额
--10 春夏秋完成率≤120%提点
--11 春夏秋完成率＞120%金额
--12 春夏秋完成率＞120%提点
--13 冬装完成率≤120%金额
--14 冬装完成率≤120%提点
--15 冬装完成率＞120%金额
--16 冬装完成率＞120%提点
-- 1 春夏秋结算金额 2 春夏秋实销金额
IF ( @type2 IN ( 3, 4 ) ) --店长提成和店助提成
BEGIN
SELECT @value31 = SUM(amount), @value32 = SUM(act_amount)
FROM dbo.Ccommission1_X2
WHERE shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
AND type = 1
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 = 2 ) --组长提成
BEGIN
SELECT @value31 = SUM(amount), @value32 = SUM(act_amount)
FROM dbo.Ccommission1_X2
WHERE (
eid IN ( SELECT eid
FROM dbo.eleader_sales
WHERE groupleader = @eid )
and eid <> @eid
)
AND type = 1
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 = 1 ) --个人提成
BEGIN
SELECT @value31 = isnull(a.amount,0) + isnull(b.amount,0), @value32 = isnull(a.act_amount,0) + isnull(b.act_amount,0)
FROM (
SELECT SUM(amount) AS amount, SUM(act_amount) act_amount
FROM dbo.Ccommission1_X2
WHERE type = 1
AND eid = @eid
AND DATEDIFF(mm, @term, term) = 0
) a ,
(
SELECT SUM(amount) / ISNULL(@num, 1) AS amount,
SUM(act_amount) / ISNULL(@num, 1) act_amount
FROM dbo.Ccommission1_X2
WHERE ISNULL(eid, 0) = 0
AND type = 1
AND DATEDIFF(mm, @term, term) = 0
AND shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
) b;
END;
--4 冬装结算金额 5冬装实销金额
IF ( @type2 IN ( 3, 4 ) ) --店长
BEGIN
SELECT @value35 = SUM(amount), @value36 = SUM(act_amount)
FROM dbo.Ccommission1_X2
WHERE shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
AND type = 2
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 = 2 ) --组长
BEGIN
SELECT @value35 = SUM(amount), @value36 = SUM(act_amount)
FROM dbo.Ccommission1_X2
WHERE (
eid IN ( SELECT eid
FROM dbo.eleader_sales
WHERE groupleader = @eid )
and eid <> @eid
)
AND type = 2
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 IN ( 1 ) ) --营业员
BEGIN
SELECT @value35 = isnull(a.amount,0) + isnull(b.amount,0), @value36 = isnull(a.act_amount,0) + isnull(b.act_amount,0)
FROM (
SELECT SUM(amount) AS amount, SUM(act_amount) act_amount
FROM dbo.Ccommission1_X2
WHERE type = 2
AND eid = @eid
AND DATEDIFF(mm, @term, term) = 0
) a ,
(
SELECT SUM(amount) / ISNULL(@num, 1) AS amount,
SUM(act_amount) / ISNULL(@num, 1) act_amount
FROM dbo.Ccommission1_X2
WHERE ISNULL(eid, 0) = 0
AND type = 2
AND DATEDIFF(mm, @term, term) = 0
AND shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
) b; END;
------------导入 线上
IF ( @type2 IN ( 3, 4 ) ) --店长提成和店助提成
BEGIN
SELECT @value33 = SUM(amount), @value34 = SUM(act_amount)
FROM dbo.Ccommission2_X2
WHERE shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
AND type = 1
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 = 2 ) --组长提成
BEGIN
SELECT @value33 = SUM(amount), @value34 = SUM(act_amount)
FROM dbo.Ccommission2_X2
WHERE (
eid IN ( SELECT eid
FROM dbo.eleader_sales
WHERE groupleader = @eid )
and eid <> @eid
)
AND type = 1
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 = 1 ) --个人提成
BEGIN
SELECT @value33 = isnull(a.amount,0) + isnull(b.amount,0), @value34 = isnull(a.act_amount,0) + isnull(b.act_amount,0)
FROM (
SELECT SUM(amount) AS amount, SUM(act_amount) act_amount
FROM dbo.Ccommission2_X2
WHERE type = 1
AND eid = @eid
AND DATEDIFF(mm, @term, term) = 0
) a ,
(
SELECT SUM(amount) / ISNULL(@num, 1) AS amount,
SUM(act_amount) / ISNULL(@num, 1) act_amount
FROM dbo.Ccommission2_X2
WHERE ISNULL(eid, 0) = 0
AND type = 1
AND DATEDIFF(mm, @term, term) = 0
AND shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
) b;
END;
--4 冬装结算金额 5冬装实销金额
IF ( @type2 IN ( 3, 4 ) ) --店长
BEGIN
SELECT @value37 = SUM(amount), @value38 = SUM(act_amount)
FROM dbo.Ccommission2_X2
WHERE shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
AND type = 2
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 = 2 ) --组长
BEGIN
SELECT @value37 = SUM(amount), @value38 = SUM(act_amount)
FROM dbo.Ccommission2_X2
WHERE (
eid IN ( SELECT eid
FROM dbo.eleader_sales
WHERE groupleader = @eid )
and eid <> @eid
)
AND type = 2
AND DATEDIFF(mm, @term, term) = 0;
END;
ELSE
IF ( @type2 IN ( 1 ) ) --营业员
BEGIN
SELECT @value37 = isnull(a.amount,0) + isnull(b.amount,0), @value38 = isnull(a.act_amount,0) + isnull(b.act_amount,0)
FROM (
SELECT SUM(amount) AS amount, SUM(act_amount) act_amount
FROM dbo.Ccommission2_X2
WHERE type = 2
AND eid = @eid
AND DATEDIFF(mm, @term, term) = 0
) a ,
(
SELECT SUM(amount) / ISNULL(@num, 1) AS amount,
SUM(act_amount) / ISNULL(@num, 1) act_amount
FROM dbo.Ccommission2_X2
WHERE ISNULL(eid, 0) = 0
AND type = 2
AND DATEDIFF(mm, @term, term) = 0
AND shop_num IN ( SELECT shopcode
FROM dbo.oDepartment
WHERE DepID = (
SELECT DepID
FROM dbo.eEmployee
WHERE EID = @eid
) )
) b;
END;
--===================================
IF ( @type = 31 )
SET @value = @value31;
ELSE
IF ( @type = 32 )
SET @value = @value32;
ELSE
IF ( @type = 35 )
SET @value = @value35;
ELSE
IF ( @type = 36 )
SET @value = @value36;
IF ( @type = 33 )
SET @value = @value33;
ELSE
IF ( @type = 34 )
SET @value = @value34;
ELSE
IF ( @type = 37 )
SET @value = @value37;
ELSE
IF ( @type = 38 )
SET @value = @value38;
RETURN ISNULL(@value,0);
END;
2539
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
