WTS考勤系统报表实现原理

CREATE proc proc_stdrpt_kq_jbsummary  
 @ReportMenuCode varchar(50),  -- 报表编码  
 @compID varchar(36),          --所属公司ID  
 @UsePage  int,                -- 0不分页 1--分页  
 @BeginRow int ,               --开始行  
 @PageSize int,                --页面长度  
 @ColList varchar(4000),       --配置列  
 @SumColList varchar(4000),    --汇总列  
 @WhereList varchar(8000),     --查询组装条件  
 @PermissionSql varchar(5000), -- 权限条件  
 @PageTmpID varchar(40),       --用于存储总记录数的临时ID  
 @Login_ID varchar(36)         --登陆者ID  
as   
begin  
 declare @permission nvarchar(2000)  
 set @permission = ' and exists (select 1 from pub_folder_temp_pub f where f.folder_uid = b.dept_uid and login_folder_type_code = 5 and login_comp_uid = ''' + @compID + ''' and login_uid=  ''' + @Login_ID + ''')'    
 if exists(select 1 from pub_user where user_uid = @Login_ID and user_type > 0 )  
 begin  
  set @WhereList = @WhereList + @permission  
 end  
 declare @dataView nvarchar(10)  
 declare @cus_Where varchar(1000)  
 select @dataView = rptstdcs_searchvalue from report_standard_search where rptstdcs_repcode='KQ_JBHZ' and rptstdcs_login_uid=@Login_ID  and rptstdcs_searchkey='data_view'  
 set @cus_Where = ' and data_view = ' + @dataView  
 --处理特殊的维度字段  
 set @WhereList = REPLACE(@WhereList,'data_view',@dataView)  
 declare @sql varchar(max)  
 declare @sql1 varchar(max)  
    set @WhereList = @WhereList + ' and b.comp_id = ''' + @compID + ''''  
    --在此处插入需要需要查询的表、视图 
    set @sql = ' select {0} from (  
				    	select * from table_name 
				     	where hz_zhangtao =''C'' ' + @WhereList + '  
				     	group by user_code   
					 union all 
					 select null ,null ,SUM(de_gs) ,SUM(ot1_gs) ,SUM(ot2_gs),SUM(ot3_gs),SUM(ot_sum_gs),1,null ,null 
					from kq_result_hz
					where 1 = 1 
                    )  d  
                where 1=1 '+@cus_Where   
 set @sql1 = REPLACE(@sql,'{0}' ,'  1 ');  
 set @sql = REPLACE(@sql,'{0}' ,@ColList)  
 --分页数据  
 if @UsePage = 1  
 begin  
  set @sql = 'select * from (select row_number() over (order by comp_pm,order_name) as idx, * from (' + @sql + ') as tmp ) out_table  
     where idx between ' + cast(@BeginRow + 1 as varchar) + ' and ' + cast(@BeginRow + @PageSize as varchar)  
  exec (@sql)      
  end   
 else   
 begin  
  exec(@sql)  

 end  
 exec(@sql1)  
 insert into pub_report_pagecount(tmp_id, count) values( @PageTmpID,@@rowcount )  
end  

-----------2020年5月22日--------------------