if exists(select 1 from sysobjects where name = 'pr_vip_fathercard_deduct')
   drop proc pr_vip_fathercard_deduct
go

create procedure pr_vip_fathercard_deduct(
	@deduct_no	varchar(18),
	@branch_no	varchar(6),
	@begin_date	char(10),
	@end_date	char(10),
	@adt_from		datetime,
	@adt_to			datetime,
	@oper_id		varchar(6)
)
WITH ENCRYPTION
as
begin
	declare
		@ls_from	char(10),
		@ls_to		char(10),
		@oper_date	datetime,
		@card_type	char(2),
		@branch_no_l varchar(6),
		@card_id	varchar(20),
		@flow_no	varchar(18),
		@item_no	varchar(20),
		@sale_money	decimal(16,4),
		@sale_qnty	decimal(16,4),
		@father_card varchar(20),
		
		@plan_no	varchar(20),
		@push_type	char(1),
		@code_no	varchar(20) ,	--提成类别/品牌/单品编码
		@code_name	varchar(20),	--名称
		@push_way	char(1),		--提成方式--按比率 0；按金额 1
		@push_amt	decimal(16,4),	--提成比率/金额
		@push_amt_m decimal(16,4),
		
		@deduct_amt	decimal(16,4),	 --提成金额
		@deduct_amt_cls decimal(16,4), --类别提成金额
		@deduct_amt_brand decimal(16,4), --品牌提成金额
        @deduct_amt_item decimal(16,4), --品牌提成金额

		@item_clsno varchar(20) ,
		@item_brand varchar(20) ,
		@ll_cnt int,
		@i int,
        @push_way_cls	char(1),		--类别提成方式--按比率 0；按金额 1
		@push_amt_cls	decimal(16,4),	--类别提成比率/金额
        @plan_no_cls	varchar(20),
        @push_way_brand	char(1),		--品牌提成方式--按比率 0；按金额 1
		@push_amt_brand	decimal(16,4),	--品牌提成比率/金额
        @plan_no_brand	varchar(20),
        @push_way_item	char(1),		--品牌提成方式--按比率 0；按金额 1
		@push_amt_item	decimal(16,4),	--品牌提成比率/金额
        @plan_no_item	varchar(20),
        @temp_cond      varchar(20),
        @deduct_type    varchar(1),
        @temp_deduct_amt numeric(16,4),
        @day             int,            --提现提现天数
        --modify by zhouhx 20200414 增加二级分销员支持 增加二级分销员支持
        @deduct_rate1 numeric(16, 4),			--一级分销员 提成占比
		@deduct_rate2 numeric(16, 4),			--二级分销员 提成占比
		@deduct_rate1_cls numeric(16, 4),
		@deduct_rate2_cls numeric(16, 4),
		@deduct_rate1_brand numeric(16, 4),
		@deduct_rate2_brand numeric(16, 4),
		@deduct_rate1_item numeric(16, 4),
		@deduct_rate2_item numeric(16, 4),
		@father_card2 varchar(20),
		@share_cardid varchar(50),
		@share_flag varchar(1),
		@onlyonline_reamt varchar(80)  --启用分销返佣只实现线上下单销售有返佣   huangzb 20201028 200923-1010134
		
	--启用分销返佣只实现线上下单销售有返佣   huangzb 20201028 200923-1010134
	select @onlyonline_reamt = isnull(sys_var_value,'0') from t_sys_system  WHERE sys_var_id = 'onlyonline_reamt' ;
	select @onlyonline_reamt=rtrim(ltrim(@onlyonline_reamt))
	if @onlyonline_reamt is null or @onlyonline_reamt=''
	begin
		select @onlyonline_reamt='0'
	end
		
		
	--销售流水（会员）表
	create table #temp_vip_sale
	(
		cond_id	int	IDENTITY(1,1) NOT NULL,	--编码标识	
		flow_no	varchar(18)	NOT NULL,		--流水号
		branch_no	varchar(6)		NULL,	--分店	
		item_no		varchar(20)  NULL,		--货号
		sale_money	numeric(16,4)  NULL,	--销售金额
		sale_qnty	numeric(16,4)  NULL,	--销售数量
		card_id		varchar(20)  NULL,	
		father_card varchar(20)  NULL,		--推荐人ID
		card_type	char(2)  NULL,		--会员等级
		oper_date	datetime,
		father_card2	varchar(20),			--二级推荐人ID
		wxsf_order		varchar(32),			--微订单标识 1：是   0：否
		share_cardid	varchar(50)null			--微信推荐人
	)
	create table #temp_plan
	(
		plan_no varchar(20) NOT NULL,		--营销方案编号
		deduct_amt numeric(16,4) null,
		deduct_type char(1) not null,		--提成类型-  B:品牌;  C:类别 ； I 单品,
        push_way char(1) null,
        push_amt numeric(16,4) null,
        deduct_rate1	numeric(16, 4) null,	--一级分销员 提成占比
        deduct_rate2	numeric(16, 4) null		--二级分销员 提成占比	
	)
	
	--变量初始化
	set @ls_from = convert(char(10), @adt_from, 121)
	set @ls_to = convert(char(10), @adt_to, 121)
	
	--先删除提成流水表中的数据
	if exists(select top 1 1 from t_vip_fathercard_deduct_flow where oper_date between @adt_from and @adt_to and branch_no like @branch_no and Fa_status not in ('1','2'))
	begin
		delete t_vip_fathercard_deduct_flow where oper_date between @adt_from and @adt_to and branch_no like @branch_no and Fa_status not in ('1','2')
		if @@error <> 0 goto err
	end
	--modify by zhouhx 20200413 退货流水记录成负数量、负金额 200403-934177
	--modify by zhouhx 20200414 增加二级分销员支持 增加二级分销员支持
	--modify by yanghc 20200724 1.非按单退货，voucher_no是空值，导致oper_date空值报错 2.多付款方式，分销提成重复问题优化 200722-983905 --200824-997611 存在微订单付款流水卡号空值的情况，优化取微订单的卡号 yanghc
	--销售流水（会员）--已经结算的数据要排除掉，不能重复结算
	insert into #temp_vip_sale(oper_date,card_id,branch_no,flow_no,item_no,sale_money,sale_qnty,card_type,father_card,father_card2,wxsf_order)	
	select  (case when b.sell_way = 'B' then isnull((select top 1 oper_date from t_rm_saleflow where flow_no = b.voucher_no),a.oper_date) else a.oper_date end) as oper_date,
			b.vip_no,a.branch_no,a.flow_no,a.item_no,
			(case when b.sell_way = 'B' then -1*a.sale_money else a.sale_money end) as sale_money,
			(case when b.sell_way = 'B' then -1*a.sale_qnty else a.sale_qnty end) as sale_qnty,
			t_rm_vip_info.card_type,
			father_card = isnull(t_rm_vip_info.father_card,''),
			father_card2 = '',
			b.wxsf_order
			--father_card2 = isnull((select father_card from t_rm_vip_info where t_rm_vip_info.card_id = isnull((select father_card from t_rm_vip_info where t_rm_vip_info.card_id = b.vip_no ),'')), '')
	from t_rm_saleflow a 
		inner join (select b.flow_no,b.sell_way,b.voucher_no,
					vip_no = case when b.vip_no is null and b.memo like '微订单:%'  
							      then isnull((select top 1 m.card_id from t_rm_vip_info m with(nolock),t_order_bill_weixin n where m.card_id = n.card_id and n.sheet_no = right(b.memo,LEN(b.memo)-4)),'')
							      else b.vip_no end,
					wxsf_order = case when b.memo like '微订单:%'  then right(b.memo,LEN(b.memo)-4) else '' end,
					re_flag=(case when b.sell_way='B' and b.voucher_no<>'' and b.voucher_no is not null
									   and exists(select 1 from t_rm_payflow pay where pay.flow_no = b.voucher_no and pay.memo like '微订单:%')
							 then '1' else '0' end)
					from t_rm_payflow b where b.flow_id = 1 and b.oper_date between @adt_from and @adt_to 
						and b.branch_no = @branch_no and (isnull(b.vip_no,'') <> '' or (b.vip_no is null and b.memo like '微订单:%'))) b  on a.flow_no = b.flow_no 
		left join t_rm_vip_info with(nolock) on b.vip_no = t_rm_vip_info.card_id
	where ( a.oper_date between @adt_from and @adt_to)
			and ( a.branch_no = @branch_no ) and isnull(b.vip_no,'') <> ''
			and  a.sale_money <> 0
			and not exists(select 1 from t_vip_fathercard_deduct_flow rt 
				where rt.flow_no=a.flow_no and oper_date between @adt_from and @adt_to 
					and branch_no like @branch_no and Fa_status = '1' )
			and ( (@onlyonline_reamt='1' and (b.wxsf_order<>''or b.re_flag='1')) or (@onlyonline_reamt='0') )  --启用分销返佣只实现线上下单销售有返佣   huangzb 20201028 200923-1010134
	order by a.branch_no
	
	--取母卡卡号的母卡 提升效率 
	update #temp_vip_sale set father_card2 = isnull(t_rm_vip_info.father_card,'')
	from t_rm_vip_info 
	where t_rm_vip_info.card_id = #temp_vip_sale.father_card
	
	--取微订单的推荐人
	update #temp_vip_sale set share_cardid = isnull(a.share_cardid,'')
	from t_order_bill_weixin a,#temp_vip_sale b 
	where a.card_id=b.card_id and a.sheet_no=b.wxsf_order and LEN(ISNULL(b.wxsf_order,''))>0
	
	--200702-975039 删除会导致cond_id 和 @i 对不上，导致提成计算有问题 yanghc 
	--会员没有母卡，则不计算提成 在临时表中删除 提升效率
	--delete from #temp_vip_sale where isnull(father_card,'') = ''

	select @ll_cnt = count(1) from #temp_vip_sale
	if @ll_cnt <= 0 
		return
	select @i =1
	while @i <= @ll_cnt
		begin
			select @oper_date =oper_date,@card_id = card_id,@branch_no_l = branch_no,@flow_no =flow_no,@item_no =item_no,
					@sale_money =isnull(sale_money,0),@sale_qnty =isnull(sale_qnty,0),@card_type =card_type,
					@father_card = isnull(father_card,''),@father_card2 = isnull(father_card2, ''),@share_cardid=share_cardid
			from #temp_vip_sale where cond_id = @i
			
			--modify by zhouhx 20200416 会员没有母卡，则不计算提成（同步商慧7 200414-938746）N200415-397565
			if(@father_card = '' or @father_card is null) and (@share_cardid = '' or @share_cardid is null)
			begin
				set @i = @i +1
				continue
			end
			
			select @item_clsno = item_clsno,@item_brand =item_brand from t_bd_item_info where item_no =@item_no
            select @item_clsno = isnull(ltrim(rtrim(@item_clsno)),'')
			select @item_brand = isnull(ltrim(rtrim(@item_brand)),'')

			--寻找最优提成方案	
            
            --类别
            select @plan_no_cls =''
            select @push_way_cls =''	
		    select @push_amt_cls = 0
            select @temp_cond = ''
			if len(@item_clsno) > 0
            begin
               select @temp_cond = @item_clsno
			   select top 1 @plan_no_cls = m.plan_no ,@push_way_cls= push_way,@push_amt_cls = push_amt,
							@deduct_rate1_cls = isnull(m.deduct_rate1, 1),
							@deduct_rate2_cls = (case when @father_card2 = '' then 0 else isnull(m.deduct_rate2, 0) end),@share_flag=m.share_flag
			   from t_vip_father_card_master m,t_vip_father_card_detail d
			   where m.plan_no = d.plan_no and (m.card_type = 'A' or (m.card_type =@card_type)) 
					and m.approve_flag ='1' and  @oper_date between m.begin_date and m.end_date
                 and d.push_type='C' and (d.code_no = @temp_cond)
			   order by m.work_date desc
			   
               while @plan_no_cls ='' or @plan_no_cls is null
               begin
                  select @temp_cond =''
                  --200824-997611 导进去的商品类别cls_parent可能是空值 yanghc 2020-08-27
                  select top 1 @temp_cond = isnull(ltrim(rtrim(cls_parent)),substring(item_clsno,1,len(item_clsno)-2)) from t_bd_item_cls where item_clsno=@item_clsno 
                  if (@temp_cond is null or @temp_cond ='') or @temp_cond =  ltrim(rtrim(@item_clsno))
                      break
                  select @item_clsno =  @temp_cond  
                  select @plan_no_cls = m.plan_no ,@push_way_cls= push_way,@push_amt_cls = push_amt,
						 @deduct_rate1_cls = isnull(m.deduct_rate1, 1),
						 @deduct_rate2_cls = (case when @father_card2 = '' then 0 else isnull(m.deduct_rate2, 0) end) ,@share_flag=m.share_flag
			      from t_vip_father_card_master m,t_vip_father_card_detail d
			      where m.plan_no = d.plan_no and (m.card_type = 'A' or (m.card_type =@card_type)) 
					and m.approve_flag ='1' and  @oper_date between m.begin_date and m.end_date
                    and d.push_type='C' and d.code_no = @temp_cond
			      order by m.work_date desc
               end
			end 
            --品牌
            select @plan_no_brand =''
            select @push_way_brand =''	
		    select @push_amt_brand = 0
            select @temp_cond = ''
            if len(@item_brand) > 0
            begin
               select @temp_cond =@item_brand
               select top 1 @plan_no_brand = m.plan_no ,@push_way_brand= push_way,@push_amt_brand = push_amt,
							@deduct_rate1_brand = isnull(m.deduct_rate1, 1),
							@deduct_rate2_brand = (case when @father_card2 = '' then 0 else isnull(m.deduct_rate2, 0) end),@share_flag=m.share_flag
			   from t_vip_father_card_master m,t_vip_father_card_detail d
			   where m.plan_no = d.plan_no and (m.card_type = 'A' or (m.card_type =@card_type)) 
					and m.approve_flag ='1' and  @oper_date between m.begin_date and m.end_date
                 and d.push_type='B' and d.code_no = @temp_cond
			   order by m.work_date desc
			   --品牌没有父级
     --          while @plan_no_brand ='' or @plan_no_brand is null
     --          begin
     --             select @temp_cond = ''
     --             select @temp_cond = ltrim(rtrim(code_id)) from t_bd_base_code where type_no = 'PP' and code_id =@item_brand 
     --             if (@temp_cond is null or @temp_cond ='') or @temp_cond =  ltrim(rtrim(@item_brand))
     --                 break
     --             select @item_brand =  @temp_cond 
     --             select @plan_no_brand = m.plan_no ,@push_way_brand= push_way,@push_amt_brand = push_amt
			  --    from t_vip_father_card_master m,t_vip_father_card_detail d
			  --    where m.plan_no = d.plan_no and (m.card_type = 'A' or (m.card_type =@card_type)) 
					--and m.approve_flag ='1' and  @oper_date between m.begin_date and m.end_date
     --               and d.push_type='B' and d.code_no = @temp_cond
			  --    order by m.work_date desc
     --          end
			end 

            --商品
            select @plan_no_item =''
            select @push_way_item =''	
		    select @push_amt_item= 0

			select top 1 @plan_no_item = m.plan_no ,@push_way_item= push_way,@push_amt_item = push_amt,
						 @deduct_rate1_item = isnull(m.deduct_rate1, 1),
						 @deduct_rate2_item = (case when @father_card2 = '' then 0 else isnull(m.deduct_rate2, 0) end),@share_flag=m.share_flag
			from t_vip_father_card_master m,t_vip_father_card_detail d
			where m.plan_no = d.plan_no and (m.card_type = 'A' or (m.card_type =@card_type)) 
				and m.approve_flag ='1' and  @oper_date between m.begin_date and m.end_date
                and d.push_type='I' and d.code_no = @item_no
			order by m.work_date desc
			
            --计算类别提成
            select @deduct_amt_cls = 0
            if len(@plan_no_cls) > 0 
            begin
               select @push_way_cls = isnull(@push_way_cls,'0')
               select @push_amt_cls = isnull(@push_amt_cls,0)
               if @push_way_cls = '0' 
                  select @deduct_amt_cls = @sale_money * @push_amt_cls
               else
                  select @deduct_amt_cls = @sale_qnty * @push_amt_cls
            end
            --计算品牌提成
            select @deduct_amt_brand = 0
            if len(@plan_no_brand) > 0 
            begin
               select @push_way_brand = isnull(@push_way_brand,'0')
               select @push_amt_brand = isnull(@push_amt_brand,0)
               if @push_way_brand = '0' 
                  select @deduct_amt_brand = @sale_money * @push_amt_brand
               else
                  select @deduct_amt_brand = @sale_qnty * @push_amt_brand
            end
            --计算商品方案提成
            select @deduct_amt_item = 0
            if len(@plan_no_item) > 0 
            begin
               select @push_way_item = isnull(@push_way_item,'0')
               select @push_amt_item = isnull(@push_amt_item,0)
               if @push_way_item = '0' 
                  select @deduct_amt_item = @sale_money * @push_amt_item
               else
                  select @deduct_amt_item = @sale_qnty * @push_amt_item
            end
            delete from #temp_plan
            if abs(@deduct_amt_cls) <>0 
               insert into #temp_plan(plan_no,deduct_amt,deduct_type,push_way,push_amt,deduct_rate1,deduct_rate2)
                values(@plan_no_cls,@deduct_amt_cls,'C',@push_way_cls,@push_amt_cls, @deduct_rate1_cls, @deduct_rate2_cls)
			if abs(@deduct_amt_brand) <>0 
               insert into #temp_plan(plan_no,deduct_amt,deduct_type,push_way,push_amt,deduct_rate1,deduct_rate2)
                values(@plan_no_brand,@deduct_amt_brand,'B',@push_way_brand,@push_amt_brand, @deduct_rate1_brand, @deduct_rate2_brand)
            if abs(@deduct_amt_item) <>0 
               insert into #temp_plan(plan_no,deduct_amt,deduct_type,push_way,push_amt,deduct_rate1,deduct_rate2)
                values(@plan_no_item,@deduct_amt_item,'I',@push_way_item,@push_amt_item, @deduct_rate1_item, @deduct_rate2_item)	
                
			select @deduct_amt = 0
            select @temp_deduct_amt = 0
            select @push_type = ''
            select @push_amt = 0   
            select @plan_no = '' 
            select @deduct_type =''
            select @temp_deduct_amt = max(abs(deduct_amt)) from #temp_plan
            select top 1 @plan_no = plan_no,@deduct_amt = deduct_amt,@deduct_type = deduct_type,
						 @push_way = push_way,@push_amt = push_amt, @deduct_rate1=deduct_rate1, @deduct_rate2 = deduct_rate2
               from #temp_plan 
             where abs(deduct_amt) = @temp_deduct_amt
            if len(@plan_no) > 0
            begin
				If ABS(isnull(@deduct_rate2, 0)) = 0 set @deduct_rate1 = 1 --如没有二级分销员或二级分销员比例等于0时，则当前提成统归一级分销员
				if @share_flag='1' 
				begin
					 set @deduct_rate1 = 1
					If ABS(@deduct_rate1) <> 0 and len(@share_cardid)>0
					begin
						insert into t_vip_fathercard_deduct_flow(father_card,card_id,deduct_no,flow_no,branch_no,oper_date,item_no,sale_qty,
							   sale_amt,deduct_amt,push_way,deduct_rate,plan_no,deduct_type,Fa_status, deduct_rate2,memo)
						select @share_cardid,@card_id,@deduct_no,@flow_no,@branch_no_l,@oper_date,@item_no,@sale_qnty,
							  @sale_money,@deduct_amt * @deduct_rate1,@push_way,@push_amt,@plan_no,@deduct_type, '0', @deduct_rate1,'微订单推荐人提成'
					end
				end
				else
				begin
					If ABS(@deduct_rate1) <> 0
					begin
						insert into t_vip_fathercard_deduct_flow(father_card,card_id,deduct_no,flow_no,branch_no,oper_date,item_no,sale_qty,
							   sale_amt,deduct_amt,push_way,deduct_rate,plan_no,deduct_type,Fa_status, deduct_rate2)
						select @father_card,@card_id,@deduct_no,@flow_no,@branch_no_l,@oper_date,@item_no,@sale_qnty,
							  @sale_money,@deduct_amt * @deduct_rate1,@push_way,@push_amt,@plan_no,@deduct_type, '0', @deduct_rate1
					end
					If Len(@father_card2) > 0 and ABS(@deduct_rate2) <> 0
					begin
						insert into t_vip_fathercard_deduct_flow(father_card,card_id,deduct_no,flow_no,branch_no,oper_date,item_no,sale_qty,
							   sale_amt,deduct_amt,push_way,deduct_rate,plan_no,deduct_type,Fa_status, deduct_rate2)
						select @father_card2,@card_id,@deduct_no,@flow_no,@branch_no_l,@oper_date,@item_no,@sale_qnty,
						  @sale_money,@deduct_amt * @deduct_rate2,@push_way,@push_amt,@plan_no,@deduct_type, '0', @deduct_rate2
					end
				end
			end
			set @i = @i +1
		end

	if @@error <> 0 goto err
	drop table #temp_vip_sale
    drop table #temp_plan
	
	----对于已经过期的数据直接当做作废处理
	--select @day=sys_var_value from t_sys_system where sys_var_id='FXConsumeAfterDay'
	--if(@day is null or @day='')
	--begin
	--	set @day=0
	--end
	--if exists(select 1 from t_vip_fathercard_deduct_flow where DATEDIFF(day,oper_date,GETDATE()) > CONVERT(int,@day) and Fa_status not in ('1','2'))
	--begin
	--	update a set Fa_status = '2',fa_date = GETDATE() from t_vip_fathercard_deduct_flow a 
	--	where DATEDIFF(day,oper_date,GETDATE()) > CONVERT(int,@day) and Fa_status not in ('1','2')
	--end
	--if @@error <> 0 goto err
	
	--modify by zhouhx 20200413 退货流水也要计算提成金 200403-934177--------begin-----------
	--屏蔽以下代码
	----查询流水表中如果存在会员退款，则更新会员分销提成表的状态由待结算（0）变为已失效（2）   huangzb 20200305  200302-138270----start---
	--if exists(select 1 from t_vip_fathercard_deduct_flow b ,t_rm_saleflow a 
	--					where b.flow_no=a.flow_no  
	--					and b.fa_status not in ('1','2')
	--					and a.sell_way='B'
	--					)
	--begin
	--	update aa set aa.Fa_status = '2',aa.fa_date = GETDATE() from t_vip_fathercard_deduct_flow aa 
	--	where aa.Fa_status not in ('1','2')
	--	and aa.flow_id in (
	--		select b.flow_id from t_vip_fathercard_deduct_flow b ,t_rm_saleflow a 
	--		where b.flow_no=a.flow_no  
	--		and b.fa_status not in ('1','2')
	--		and a.sell_way='B'
	--	)
	--end
	--if @@error <> 0 goto err
	----查询流水表中如果存在会员退款，则更新会员分销提成表的状态由待结算（0）变为已失效（2）   huangzb 20200305  200302-138270----end-----
	--modify by zhouhx 20200413 退货流水也要计算提成金 200403-934177--------end-----------
	
	return
err:
    drop table #temp_vip_sale
    drop table #temp_plan
	return
	
end
go
