2,209
社区成员
发帖
与我相关
我的任务
分享
create proc p_Indicators_MonthAnalysis
(
@orga varchar(50), --机构编码
@date1 varchar(50), --开始日期
@date2 varchar(50) --结束日期
)
as
begin
declare @strsql nvarchar(4000)
declare @syear varchar(10)
declare @ind_head nvarchar(1000)
declare @ind_foot nvarchar(1000)
declare @ind_body nvarchar(4000)
set @syear = datename(year,@date1)
select @ind_head =
' declare @orga varchar(30)
declare @date1 datetime
declare @date2 datetime
declare @year int
select @orga='''+@orga+''',@date1='''+@date1+''',@date2='''+@date2+''',@year='+@syear+'
declare @tmp table([id] int identity,[指标ID] int,[指标名称] nvarchar(256),[预算阶次ID] int,[预算阶次] nvarchar(20),[基本分] dec(8,2),[阶次系数] dec(8,2),[去年同期] dec(18,2),[本期预算数] dec(18,2),[实际完成数] dec(18,2),[完成率] dec(18,4),[实际得分] dec(18,2))'
, @ind_foot = '
select [id],[指标ID],[指标名称],[预算阶次ID],[预算阶次],[基本分],[阶次系数],[去年同期],[本期预算数],[实际完成数],[完成率],[实际得分] from @tmp order by [id]'
--查询基本指标
--查询业务收入、收支差额
set @ind_body = [dbo].[f_q_MonthAnalysis_base_byname]('业务收入')
if len(@ind_body)>0 exec(@ind_head+@ind_body+@ind_foot)
set @ind_body = [dbo].[f_q_MonthAnalysis_base_byname]('收支差额')
if len(@ind_body)>0 exec(@ind_head+@ind_body+@ind_foot)
end
go
create function f_q_MonthAnalysis_base_byname
(
@item nvarchar(50) --项目名称
)
returns nvarchar(4000)
as
begin
declare @tmp nvarchar(4000)
select @tmp = '
insert into @tmp([指标ID],[指标名称],[基本分],[预算阶次ID],[预算阶次],[阶次系数],[去年同期],[本期预算数],[实际完成数])
select top 1 a.[id],a.[指标名称],['+@item+'基本分] as [基本分值],d.[id],d.[阶次名称],[系数]
,(case when exists(select [实际上报] from [PracticalIndicators_Report]
where dateadd(year,-1,[上报年月]) between @date1 and @date2
and [机构编码] like @orga and [考核指标ID]=a.[id]) then (select sum([实际上报]) from [PracticalIndicators_Report]
where dateadd(year,-1,[上报年月]) between @date1 and @date2
and [机构编码] like @orga and [考核指标ID]=a.[id]) else '+[dbo].[f_q_comp_byexp]([计算表达式],-1,0,0)+' end) as [去年同期]
,'+[dbo].[f_q_budg_byexp]([计算表达式])+' as [本期预算数]
,(case when exists(select [实际上报] from [PracticalIndicators_Report] where [上报年月] between @date1 and @date2
and [机构编码] like @orga and [考核指标ID]=a.[id]) then (select sum([实际上报]) from [PracticalIndicators_Report]
where [上报年月] between @date1 and @date2
and [机构编码] like @orga and [考核指标ID]=a.[id]) else '+[dbo].[f_q_comp_byexp]([计算表达式],0,0,0)+' end) as [实际完成数]
from [BasicIndicators_Info] as a, [MonthPoints_parameters] as b
,[AdvancedPlan_Info] as c,[Budget_Order] as d,[dbo].[f_OrderCoefficient_Year](@year) as e
where a.[指标名称] like '''+@item+''' and datepart(year,b.[生效年月]) <= @year
and d.[id]=c.['+@item+'阶次ID] and [机构编码] like @orga and c.[年度]=@year and e.[阶次ID] = d.[id]
order by b.[生效年月] desc
update @tmp set [完成率]=[dbo].[f_ind_rate_of_progress]([本年预算数],[实际完成数]),[实际得分]=[dbo].[f_ind_allot_scoring]('+(case when @item like '业务收入' then '0' else '1' end)+',@year,[预算阶次ID],[本年预算数],[实际完成数])'
from [BasicIndicators_Info] where [指标名称] like @item
return (@tmp)
end
go
create function f_q_budg_byexp --为了减少字符串长度,使用简写名称,全名:[f_get_query_budget_by_expression]
(
@expression nvarchar(512) --表达式,由一系列数据项和运算操作符组合而成。
)
returns nvarchar(4000)
as
begin
declare @strsql nvarchar(4000)
--先按样式表中的项目查,如果在样式表中不存在,再去查询表达式元素表
if (len(@expression) > 1 and left(@expression,1) = '=')
begin
declare @i int
declare @j int
declare @l int
declare @ctype int
declare @chr nchar
declare @tmp nvarchar(64)
select @i = 2, @l = len(@expression), @strsql = ''
while @i <= @l
begin
set @chr = substring(@expression,@i,1)
if len(@chr) > 0 --忽略空格字符
begin
set @ctype = [dbo].[f_emelenttype_by_char](@chr)
set @j = (case @ctype when 1 then [dbo].[f_endpoint_by_numeric](@expression,@i)
when 2 then charindex(']',@expression,@i)+1
else @i + 1 end)
set @tmp = substring(@expression,@i,@j-@i)
set @strsql = @strsql + (
case @ctype when 1 then [dbo].[f_value_by_numeric](@tmp) --操作数,转换为数值
when 2 then [dbo].[f_q_budg_byeme](@tmp) --元素项目,转换为查询数据的查询语句
else [dbo].[f_expressionchar_by_char](@tmp) end) --运算操作符,特号等,转换为正确的符号
set @i = @j
end
else
set @i = @i + 1
end
end
return (case when len(@strsql) = 0 then '0' else replace([dbo].[f_empty_byexp](@strsql),'''','''''') end)
end
go