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日--------------------