EHR

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;