--查询清算明细 张凤仪 2012-1-13
USE [L2SettleDB]
GO
/****** Object:  StoredProcedure [dbo].[reader_proc]    Script Date: 01/13/2012 13:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO 

--drop procedure [dbo].[reader_proc]  --删除存储过程

create procedure [dbo].[reader_proc]
(
@dt datetime,                        --起始日期
@day int,                            --天数
@mcht varchar(15),                   --商户号
@sett varchar(8),                    --清算号
@term_id varchar(1000),              --子门店号
@CurrPage int,                       --当前页码
@PageSize int,                       --每页记录数
@raing_sum float output,             --
@PER_sum float output,               --
@raing int output,                   --
@RSA_sum float output,               --
@recordcount int output             --记录总数
)
as
declare @settle_mode varchar(1)      --清算模式
declare @mcht_role_type varchar(3)   --商户角色类型
declare @da varchar(10)              --日期字符串
declare @da1 varchar(10)             --
declare @sett1 varchar(50)           --存储清算号条件
declare @sql varchar(max)            --拼接SQL字符串
declare @dt1 varchar(10)             --起始日期
declare @i int                       --循环控制条件
declare @startdate datetime          --起始时间
declare @enddate datetime            --结束时间
set @i=0                             --循环控制条件初始值
set @dt1=@dt

set @startdate=GETDATE()   --记录开始时间
while (@i<=@day)
  begin
  set @dt=DATEADD(DAY,@i,@dt1)
  --set @da=CONVERT(char(8),current_timestamp,101)
  set @da=CONVERT(char(8), @dt,112)
  if left(@sett,2)<>'96' and len(@sett)>0
    set @sett1=' and term_id=''' + @sett +''''
  else
    set @sett1=''
    
  if @term_id='a'
     begin  
       if @i=0  --拼SQL语句
         set @sql='select * from [L2SettleDB].[dbo].[L2_L'+@da+'] where merch_id='''+@mcht+'''' +@sett1
       else
          set @sql=@sql + ' union all select * from [L2SettleDB].[dbo].[L2_L'+@da+'] where merch_id='''+@mcht+'''' +@sett1
     end 
  else
     begin  
       if @i=0  --拼SQL语句
         set @sql='select * from [L2SettleDB].[dbo].[L2_L'+@da+'] where merch_id='''+@mcht+''' and term_id in (' + @term_id + ')'
       else
          set @sql=@sql + ' union all select * from [L2SettleDB].[dbo].[L2_L'+@da+'] where merch_id='''+@mcht+''' and term_id in (' + @term_id + ')'
     end
  select @i=@i+1 
  if @i>@day
    break
end

if OBJECT_ID('[tempdb].[dbo].#t') is not null  --判断临时表是否存在，存在则删除
 drop table #t
select * into #t from [L2SettleDB].[dbo].[L2_L20110101] where 1=2
insert into #t exec(@sql)
select @settle_mode=(select sett_mode from [L2SettleDB].[dbo].[l2_mcht] where mcht_id=@mcht)
select @mcht_role_type=(select mcht_role_type from [L2SettleDB].[dbo].[l2_mcht] where mcht_id=@mcht)
select @raing_sum=(select sum(tranamount) from #t)

if @mcht_role_type='101'   --统计汇总
  select @PER_sum=(select SUM(acq_mcht_fee_value) from #t)
else if @mcht_role_type='110'
  select @PER_sum=(select SUM(iss_mcht_fee_value) from #t) 
else
  select @PER_sum=(select SUM(agent_mcht_fee_value) from #t)
  
if @settle_mode='1'
  begin
    if @mcht_role_type='101'
      begin
        if @sett<>''
          begin
            select @RSA_sum=(select sum(per_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht + ' and acq_term_id= ' + @sett )
            select @raing=(select sum(per_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_transaction]  where acq_mcht_id= + @mcht + ' and acq_term_id= ' + @sett )
          end
        else
          begin
            select @RSA_sum=(select sum(per_trans_amt_successful) from [L2SettleDB].[dbo].[l2_sum_transaction] where acq_mcht_id= + @mcht)
            select @raing=(select sum(per_trans_num_successful) from [L2SettleDB].[dbo].[l2_sum_trans