if exists(select 1 from sysobjects where name='pr_wxsf_distribution_orderdetails')
  drop procedure pr_wxsf_distribution_orderdetails
go



CREATE procedure pr_wxsf_distribution_orderdetails  
@cardid nvarchar(50), --分销员卡号
@status nvarchar(10),--状态 0 待结算 1 可提现 2已提现 3失效
@pageindex int ,--页码
@pagesize int,--每页获取数量
@total int out --总行数
WITH ENCRYPTION
as
begin
--主表
   create table #masttb
   (
	rowid INT IDENTITY(1,1),
    orderno nvarchar(50),
    cardid nvarchar(50),
    pay_amount  decimal(18,2),
    royalty_amount decimal(18,2),
    state nvarchar(10),
    pay_time datetime,
    settlement_time datetime,
    cash_time datetime,
    branch_no nvarchar(20),
    voucher_no nvarchar(50),
   )
 --商品明细表
  create table #itemtb
  (
   orderno nvarchar(50),
   item_no nvarchar(50),
   item_name nvarchar(50),
   item_price decimal(18,2),
   [count] int,
   item_specs nvarchar(20),
   sale_price decimal(18,2)

  ) 
  declare @day int --可提现的间隔天数
  if not exists(select 1_var_value from t_sys_system where sys_var_id='FXConsumeAfterDay')
  begin
   set @day=0
  end
  else
   begin
    select @day=sys_var_value from t_sys_system where sys_var_id='FXConsumeAfterDay'
   end 
  
 
 --获取待结算信息
  if(@status='0')
  begin
  
  
         select @total=count(1)
         from(select res.flow_no
         from t_vip_fathercard_deduct_flow  res
         where res.father_card=@cardid and ISNULL(res.Fa_status,'0') not in('1','2')
         --and  DATEDIFF (day,res.oper_date,GETDATE())<CONVERT(int,@day) 
         group by res.flow_no)t
         
         --写入单号
         insert into #masttb(orderno,pay_time,royalty_amount,state)
         select flow_no,oper_date,deduct_amt,state 
         from(select flow_no,oper_date 
         ,SUM(deduct_amt) as deduct_amt,'待结算' as state
         from t_vip_fathercard_deduct_flow res
         where father_card=@cardid   and ISNULL(res.Fa_status,'0') not in('1','2') 
         --and  DATEDIFF (day,res.oper_date,GETDATE())<CONVERT(int,@day) 
         group by flow_no,oper_date)t
         
         --SELECT orderno,pay_time,royalty_amount,state 
         --FROM #masttb
         --WHERE RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         DELETE FROM #masttb WHERE RowId NOT BETWEEN @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         
         --更新微订单单号
         update A set A.voucher_no=pay.voucher_no from  #masttb A inner join t_rm_payflow pay
         on A.orderno=pay.flow_no
         
         --更新会员信息
         update A SET A.cardid=pay.vip_no FROM #masttb A inner join t_rm_payflow pay on A.orderno=pay.flow_no
         
         --更新实际付款金额,门店
         UPDATE A SET A.pay_amount=case isnull(A.voucher_no,'') when '' then (select top 1 isnull(sale_amount,0) from t_rm_payflow pay
          where pay.flow_no=a.orderno and sell_way='A' and flow_id='1')
          else (select SUM(ISNULL(amount,0)) from t_wxsf_pay_list where orderno=A.voucher_no and paymode in('RMB','SAV','WZF','VIP',           'SWX')) end 
         FROM #masttb A
        
         UPDATE A SET A.branch_no=pay.branch_no from #masttb A inner join t_rm_payflow pay
         on A.orderno=pay.flow_no
        
         UPDATE A SET A.settlement_time=res.oper_date,cash_time=res.fa_date 
         from #masttb A inner join t_vip_fathercard_deduct_flow res
         on A.orderno=res.flow_no
         
		--insert into #masttb(orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,cash_time,branch_no)
	 --   select orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,settlement_time,branch_no from(
	 --   select ROW_NUMBER() over(order by pay.oper_date desc) AS RowId, res.flow_no as orderno,ISNULL(vip.mobile,vip.card_id) 
	 --    as cardid,
	 --   pay.pay_amount as pay_amount,ISNULL(SUM(res.deduct_amt),0) as royalty_amount,'待结算'
		-- as [state],
	 --  pay.oper_date as pay_time,res.cal_date as settlement_time, res.fa_date as cash_time,pay.branch_no
	 --  from t_vip_fa_plan_result res inner join t_rm_payflow pay on res.flow_no=pay.flow_no
		--inner join t_rm_vip_info vip on pay.card_id=vip.card_id
	 --  where pay.sell_way='A' and res.referer_id=@cardid
	 --  and res.Fa_status not in('1','2') and pay.pay_way not in('SHP')
	 --  group by res.flow_no,vip.mobile,vip.card_id, pay.pay_amount,pay.oper_date,res.cal_date,res.Fa_status,res.oper_date,res.fa_date,      orderno,pay.branch_no
  --      ) t
  --     WHERE t.RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
  end
  else if(@status='1')
  begin
      
      
         select @total=count(1)
         from(select res.flow_no
         from t_vip_fathercard_deduct_flow res
        where  res.father_card=@cardid and ISNULL(res.Fa_status,'0') not in('1','2')
        and  DATEDIFF (day,res.oper_date,GETDATE())>=CONVERT(int,@day) 
        group by res.flow_no)t
       
       
         insert into #masttb(orderno,pay_time,royalty_amount,state)
         select flow_no,oper_date,deduct_amt,state 
         from(SELECT flow_no,oper_date,SUM(deduct_amt) as deduct_amt,'可提现' as state
         from t_vip_fathercard_deduct_flow res
         where father_card=@cardid   and  DATEDIFF (day,res.oper_date,GETDATE())>=CONVERT(int,@day) 
	      and isnull(res.Fa_status,'0')='0' 
         group by flow_no,oper_date)t
         
         --SELECT orderno,pay_time,royalty_amount,state 
         --FROM #masttb
         --WHERE RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         DELETE FROM #masttb WHERE RowId NOT BETWEEN @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         
         --更新会员信息
         update A SET A.cardid=pay.vip_no FROM #masttb A inner join t_rm_payflow pay on A.orderno=pay.flow_no
         
          --更新实际付款金额,门店
         
         UPDATE A SET A.pay_amount=case isnull(A.voucher_no,'') when '' then (select top 1 isnull(sale_amount,0) from t_rm_payflow pay
          where pay.flow_no=a.orderno and sell_way='A' and flow_id='1')
          else (select SUM(ISNULL(amount,0)) from t_wxsf_pay_list where orderno=A.voucher_no and paymode in('RMB','SAV','WZF','VIP',           'SWX')) end 
         FROM #masttb A
        
         UPDATE A SET A.branch_no=pay.branch_no from #masttb A inner join t_rm_payflow pay
         on A.orderno=pay.flow_no
        
         UPDATE A SET A.settlement_time=res.oper_date,cash_time=res.fa_date 
         from #masttb A inner join t_vip_fathercard_deduct_flow res
         on A.orderno=res.flow_no
       
	 --  insert into #masttb(orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,cash_time,branch_no)
	 --  select orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,settlement_time,branch_no from(
	 --  select ROW_NUMBER() over(order by pay.oper_date desc) AS RowId, res.flow_no as orderno,ISNULL(vip.mobile,vip.card_id) 
	 --  as cardid,
	 --  pay.pay_amount as pay_amount,ISNULL(SUM(res.deduct_amt),0) as royalty_amount,'可提现'
		-- as [state],
	 --  pay.oper_date as pay_time,res.cal_date as settlement_time, res.fa_date as cash_time,pay.branch_no
	 --  from t_vip_fa_plan_result res inner join t_rm_payflow pay on res.flow_no=pay.flow_no
		--inner join t_rm_vip_info vip on pay.card_id=vip.card_id
	 -- where pay.sell_way='A' and res.referer_id=@cardid and  DATEDIFF (day,res.oper_date,GETDATE())>=CONVERT(int,@day) 
	 -- and isnull(res.Fa_status,'0')='0' and pay.pay_way not in('SHP')
	 -- group by res.flow_no,vip.mobile,vip.card_id, pay.pay_amount,pay.oper_date,res.cal_date,res.Fa_status,res.oper_date,res.fa_date,     orderno,pay.branch_no
  --) t
  --WHERE t.RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
  end
 else if(@status='2')
  begin
  
        select @total=count(1)
         from(select res.flow_no
         from t_vip_fathercard_deduct_flow res
        where  res.father_card=@cardid and ISNULL(res.Fa_status,'0') ='1' 
        group by res.flow_no)t
  
         
       insert into #masttb(orderno,pay_time,royalty_amount,state)

         select flow_no,oper_date,deduct_amt,state 
         from(select flow_no,oper_date 
         ,SUM(deduct_amt) as deduct_amt,'已提现' as state
         from t_vip_fathercard_deduct_flow res
         where father_card=@cardid  and ISNULL(res.Fa_status,'0') ='1'
         group by flow_no,oper_date)t   
               
         --SELECT orderno,pay_time,royalty_amount,state 
         --FROM #masttb
         --WHERE RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         DELETE FROM #masttb WHERE RowId NOT BETWEEN @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         
         --更新会员信息
         update A SET A.cardid=pay.vip_no FROM #masttb A inner join t_rm_payflow pay on A.orderno=pay.flow_no
         
         --更新实际付款金额,门店
         
         UPDATE A SET A.pay_amount=case isnull(A.voucher_no,'') when '' then (select top 1 isnull(sale_amount,0) from t_rm_payflow pay
          where pay.flow_no=a.orderno and sell_way='A' and flow_id='1')
          else (select SUM(ISNULL(amount,0)) from t_wxsf_pay_list where orderno=A.voucher_no and paymode in('RMB','SAV','WZF','VIP',           'SWX')) end 
         FROM #masttb A
        
         UPDATE A SET A.branch_no=pay.branch_no from #masttb A inner join t_rm_payflow pay
         on A.orderno=pay.flow_no
        
         UPDATE A SET A.settlement_time=res.oper_date,cash_time=res.fa_date 
         from #masttb A inner join t_vip_fathercard_deduct_flow res
         on A.orderno=res.flow_no
         
         
         
  
	 --  insert into #masttb(orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,cash_time,branch_no)
	 --  select orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,settlement_time,branch_no from(
	 --  select ROW_NUMBER() over(order by pay.oper_date desc) AS RowId, res.flow_no as orderno,ISNULL(vip.mobile,vip.card_id) 
	 --  as cardid,
	 --  pay.pay_amount as pay_amount,ISNULL(SUM(res.deduct_amt),0) as royalty_amount,'已提现'
		-- as [state],
	 --  pay.oper_date as pay_time,res.cal_date as settlement_time, res.fa_date as cash_time,pay.branch_no
	 --  from t_vip_fa_plan_result res inner join t_rm_payflow pay on res.flow_no=pay.flow_no
		--inner join t_rm_vip_info vip on pay.card_id=vip.card_id
	 -- where pay.sell_way='A' and res.referer_id=@cardid  
	 -- and res.Fa_status ='1' and pay.pay_way not in('SHP')
	 -- group by res.flow_no,vip.mobile,vip.card_id, pay.pay_amount,pay.oper_date,res.cal_date,res.Fa_status,res.oper_date,res.fa_date,     orderno,pay.branch_no
  --) t
  --WHERE t.RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
  end  
  else if(@status='3')
  begin
        select @total=count(1) 
        from(select res.flow_no
         from t_vip_fathercard_deduct_flow res inner join t_rm_payflow pay on res.flow_no=pay.flow_no
       inner join t_rm_vip_info vip on pay.vip_no=vip.card_id
        where pay.sell_way='A' and res.father_card=@cardid  and ISNULL(res.Fa_status,'0') ='2' and pay.pay_way not in('SHP')
         group by res.flow_no,vip.mobile,vip.card_id, pay.pay_amount,pay.oper_date,res.oper_date,res.Fa_status,res.oper_date,res.fa_date,pay.flow_no)t
  
  
      select @total=count(1)
         from(select res.flow_no
         from t_vip_fathercard_deduct_flow res
        where  res.father_card=@cardid  and ISNULL(res.Fa_status,'0') ='2'
        group by res.flow_no)t
  
  
        insert into #masttb(orderno,pay_time,royalty_amount,state)
         select flow_no,oper_date,deduct_amt,state 
         from(select flow_no,oper_date 
         ,SUM(deduct_amt) as deduct_amt,'已失效' as state
         from t_vip_fathercard_deduct_flow res
         where father_card=@cardid  and  ISNULL(res.Fa_status,'0') ='2' 
         group by flow_no,oper_date)t
         
         --SELECT orderno,pay_time,royalty_amount,state 
         --FROM #masttb
         --WHERE RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         DELETE FROM #masttb WHERE RowId NOT BETWEEN @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
         
         --更新会员信息
         update A SET A.cardid=pay.vip_no FROM #masttb A inner join t_rm_payflow pay on A.orderno=pay.flow_no
         
          --更新实际付款金额,门店
         
         UPDATE A SET A.pay_amount=case isnull(A.voucher_no,'') when '' then (select top 1 isnull(sale_amount,0) from t_rm_payflow pay
          where pay.flow_no=a.orderno and sell_way='A' and flow_id='1')
          else (select SUM(ISNULL(amount,0)) from t_wxsf_pay_list where orderno=A.voucher_no and paymode in('RMB','SAV','WZF','VIP',           'SWX')) end 
         FROM #masttb A
        
         UPDATE A SET A.branch_no=pay.branch_no from #masttb A inner join t_rm_payflow pay
         on A.orderno=pay.flow_no
        
         UPDATE A SET A.settlement_time=res.oper_date,cash_time=res.fa_date 
         from #masttb A inner join t_vip_fathercard_deduct_flow res
         on A.orderno=res.flow_no
         
  
  
	 --  insert into #masttb(orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,cash_time,branch_no)
	 --  select orderno,cardid,pay_amount,royalty_amount,state,pay_time,settlement_time,settlement_time,branch_no from(
	 --  select ROW_NUMBER() over(order by pay.oper_date desc) AS RowId, res.flow_no as orderno,ISNULL(vip.mobile,vip.card_id) 
	 --  as cardid,
	 --  pay.pay_amount as pay_amount,ISNULL(SUM(res.deduct_amt),0) as royalty_amount,'已失效'
		-- as [state],
	 --  pay.oper_date as pay_time,res.cal_date as settlement_time, res.fa_date as cash_time,pay.branch_no
	 --  from t_vip_fa_plan_result res inner join t_rm_payflow pay on res.flow_no=pay.flow_no
		--inner join t_rm_vip_info vip on pay.card_id=vip.card_id
	 -- where pay.sell_way='A' and res.referer_id=@cardid  
	 -- and res.Fa_status ='2' and pay.pay_way not in('SHP')
	 -- group by res.flow_no,vip.mobile,vip.card_id, pay.pay_amount,pay.oper_date,res.cal_date,res.Fa_status,res.oper_date,res.fa_date,     orderno,pay.branch_no
  --) t
  --WHERE t.RowId between @pageSize*(@pageIndex-1) + 1 and @pageSize*@pageIndex
  end  
 
  --SELECT * FROM t_im_branch_stock
  
  insert into #itemtb(orderno,item_no,item_name,item_price,[count],item_specs,sale_price)
  select t.orderno, item.item_no,item.item_name,
  --dbo.fn_get_item_price_base(item.item_no,t.branch_no,'2') as item_price,
  ISNULL(price.sale_price,item.sale_price) as item_price,  
  SUM(isnull(sale.sale_qnty,0)) as [count],
  item.item_size as item_specs,sale.sale_price
  from t_rm_saleflow sale LEFT JOIN t_pc_branch_price price ON price.item_no = sale.item_no AND price.branch_no = sale.branch_no,
		t_bd_item_info item,#masttb t
  where sale.item_no=item.item_no and sale.flow_no=t.orderno
  and sale.sell_way='A'
  group by item.item_no,item.item_name,item.price ,t.orderno,item.item_size,item.price,sale.sale_price,t.branch_no,price.sale_price,item.sale_price
  
 select * from #masttb 
 select * from #itemtb

end
go