if exists(select 1 from sysobjects where name='pr_vip_fathercard_deduct')
  drop procedure 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),
	@auto_flag	CHAR(4) = ''
)
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),
        @deduct_time DATETIME
        
        
	SELECT @deduct_time = GETDATE()

	--销售流水（会员）表
	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
	)
	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	
	)
	
	--数据库后台自动计算获取变量值
	IF @auto_flag = 'auto'
	BEGIN
		DECLARE @out_sheetno NUMERIC,@v_no VARCHAR(50),@today VARCHAR(8)
	
		--提成单号@deduct_no---
		SELECT @today = RIGHT(CONVERT(VARCHAR(8),GETDATE(),112),6)
	
		select @out_sheetno = (isnull(sheet_value,0) + 1)
		from t_sys_sheet_no
		where Lower(sheet_id) = Lower('FD');
		
		IF @out_sheetno = 0 OR @out_sheetno IS NULL
		BEGIN
			SET @out_sheetno =1	
			INSERT INTO t_sys_sheet_no 
			(  sheet_id, trans_type, sheet_name, sheet_value, sheet_type, sheet_flag, dw_code, approve_flag, w_code, parm, master_dwo, detail_dwo, current_temp, 
						  default_price1, default_price2, modify_price, up_rate, dn_rate)
			VALUES ('FD', 	'1', 	'会员分销提成', @out_sheetno, 	'2', 	'', 		'', 	'0', 			'',		'', 	'', 		'', 	'', 	
							'0', 			'0', 			'0', 		NULL, 	 NULL);
		end
		ELSE
		begin
			if @out_sheetno>9999  
				SET @out_sheetno =1	 
			update t_sys_sheet_no set sheet_value= @out_sheetno where sheet_id = 'FD';		
		END
		
		if @@error <> 0 goto err
		SET @v_no = RIGHT(REPLICATE('0',4) + RTRIM(CAST(@out_sheetno AS VARCHAR(4))),4)
		SET @deduct_no = upper('FD') + '000' + @today + @v_no
		--分店r
		SET @branch_no = '%'
		SET @begin_date = CONVERT(VARCHAR(10),GETDATE(),120)
		SET @end_date = @begin_date
		SET @adt_from = @begin_date + ' 00:00:00'
		SET @adt_to = @end_date + ' 23:59:59.997'

	END

	--变量初始化
	set @ls_from = convert(char(10), @adt_from, 121)
	set @ls_to = convert(char(10), @adt_to, 121)
	
	--先删除提成流水表中的数据
	--增加提现状态为0未提现的才删除***2019-12-09*********panjq
	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)
	begin
		delete t_vip_fathercard_deduct_flow 
		where oper_date between @adt_from and @adt_to and branch_no like @branch_no
		AND ISNULL(Fa_status,'0') = '0'
		AND NOT EXISTS(select 1 from t_rm_saleflow sale where sale.flow_no=t_vip_fathercard_deduct_flow.flow_no)
		if @@error <> 0 goto err
	end
	--销售流水（会员）
	--增加提现状态***2019-11-28*********panjq
	insert into #temp_vip_sale(oper_date,card_id,branch_no,flow_no,item_no,sale_money,sale_qnty,card_type,father_card)	
	select a.oper_date,b.vip_no,a.branch_no,a.flow_no,a.item_no, a.sale_money,a.sale_qnty,t_rm_vip_info.card_type,
		father_card = 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 t_rm_payflow b ON a.flow_no = b.flow_no left join t_rm_vip_info on b.vip_no= t_rm_vip_info.card_id
	where ( a.oper_date between @adt_from and @adt_to)
			AND b.flow_id = 1
			and ( a.branch_no like @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 )
	order by a.branch_no
		
	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,'')
			from #temp_vip_sale where cond_id = @i
			
			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
			   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 =''
                  select @temp_cond = ltrim(rtrim(cls_parent)) 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 TOP 1 @plan_no_cls = m.plan_no ,@push_way_cls= push_way,@push_amt_cls = 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='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 @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
               while @plan_no_brand ='' or @plan_no_brand is null
               begin
                  select @temp_cond = ''
                  --select @temp_cond = ltrim(rtrim(brand_parent)) from t_bd_item_brand where item_brandno=@item_brand 
                  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
			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)
                values(@plan_no_cls,@deduct_amt_cls,'C',@push_way_cls,@push_amt_cls)
			if abs(@deduct_amt_brand) <>0 
               insert into #temp_plan(plan_no,deduct_amt,deduct_type,push_way,push_amt)
                values(@plan_no_brand,@deduct_amt_brand,'B',@push_way_brand,@push_amt_brand)
            if abs(@deduct_amt_item) <>0 
               insert into #temp_plan(plan_no,deduct_amt,deduct_type,push_way,push_amt)
                values(@plan_no_item,@deduct_amt_item,'I',@push_way_item,@push_amt_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
               from #temp_plan 
             where abs(deduct_amt) = @temp_deduct_amt
             --print @temp_deduct_amt	
            if len(@plan_no) > 0
			   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,deduct_time)
			    select @father_card,@card_id,@deduct_no,@flow_no,@branch_no_l,@oper_date,@item_no,@sale_qnty,
                      @sale_money,@deduct_amt,@push_way,@push_amt,@plan_no,@deduct_type,@deduct_time
			
			--select * into #temp_plan_jq FROM #temp_plan
            --print @plan_no	
			set @i = @i +1
		end

	if @@error <> 0 goto err
	drop table #temp_vip_sale
    drop table #temp_plan
	
	return
err:
    drop table #temp_vip_sale
    drop table #temp_plan
	return
	
end
go