经典存储过程计算报表

     用单一存储过程计算报表,替换以前用java+Hibernate的给客户做的计算报表,客户每次用后系统慢等各种问题,该存储过程是当时花了近一周时间,看以前的逻辑,修改的,一开始觉得很简单,但越做越复杂,中途都想放弃,最终坚持下来,完整的替换了以前程序单条SQL计算,保留下来脚本,做纪念!
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getbetweenDays]'AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   
DROP FUNCTION [dbo].[getbetweenDays]
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDay]'AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   
DROP FUNCTION [dbo].[getDay]
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getDays]'AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   
DROP FUNCTION [dbo].[getDays]
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hjtj]'AND type in (N'P', N'PC'))
   
DROP PROCEDURE [dbo].[hjtj]
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hjtjold]'AND type in (N'P', N'PC'))
   
DROP PROCEDURE [dbo].[hjtjold]
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
 Author:       
--
 Create date: 2008年7月9日9:30:49
--
 Description:    比较两个时间段间交叉的天数
--
 判断(begin1,end1)时间段在(@begin2,@end2)中有几天(包含当天)
--
 调用[dbo].[getDays]('2008-06-06','2008-07-06','2008-07-01','2008-07-09')
--
 意思是:2008-06-06到2008-07-06中在'2008-07-01'到'2008-07-09'有几天
--
 =============================================
create function [dbo].[getbetweenDays]
(
@begin1 varchar(12),@end1 varchar(12),@begin2 varchar(12),@end2 varchar(12),@begin3 varchar(12),@end3 varchar(12))
returns int 
as  
begin
 
declare @num as int;
 
declare @beginDate as datetime;
 
declare @endDate as datetime;
 
declare @beginDate1 as datetime;
 
declare @endDate1 as datetime;
 
declare @beginDate2 as datetime;
 
declare @endDate2 as datetime;
 
set @beginDate=cast(@begin1 as datetime);
 
set @endDate=cast(@end1 as datetime);
 
set @beginDate1=cast(@begin2 as datetime);
 
set @endDate1=cast(@end2 as datetime);
 
set @beginDate2=cast(@begin3 as datetime);
 
set @endDate2=cast(@end3 as datetime);
 
set @num=0;
 
--两个时间交集
   if(@begindate2<=@begindate1 and @enddate2<=@endDate1)
   
set @num=datediff(day,@begindate,@enddate)+1;
 
return @num;
end
go
-- =============================================
--
 Author:        
--
 Create date: 2008年7月9日9:30:49
--
 Description:    比较两个字符串间的天数
--
 判断(@begin时间在(@end)中有几天(包含当天)
--
 调用[dbo].[getDays]('2008-06-06','2008-07-06')
--
 意思是:2008-06-06到2008-07-06有几天
--
 =============================================
CREATE function [dbo].[getDay](
  
@begin varchar(12),
  
@end varchar(12)
)
returns int
begin
 
declare @num as int
 
set @num=0;
 
declare @beginDate as datetime;
 
declare @endDate as datetime;
 
if(@begin is null and @end is null)
  
return 100000000;
 
if(@begin>@end)
  
return 100000000;
 
set @beginDate=cast(@begin as datetime)
 
set @endDate=cast(@end as datetime)
 
set @num=datediff(day,@begindate,@enddate)+1;
 
return @num;
end

GO
-- =============================================
--
 Author:        
--
 Create date: 2008年7月9日9:30:49
--
 Description:    比较两个时间段间交叉的天数
--
 判断(begin1,end1)时间段在(@begin2,@end2)中有几天(包含当天)
--
 调用[dbo].[getDays]('2008-06-06','2008-07-06','2008-07-01','2008-07-09')
--
 意思是:2008-06-06到2008-07-06中在'2008-07-01'到'2008-07-09'有几天
--
 =============================================
CREATE function [dbo].[getDays]
(
@begin1 varchar(12),@end1 varchar(12),@begin2 varchar(12),@end2 varchar(12))
returns int
as  
begin
 
declare @num as int;
 
declare @beginDate as datetime;
 
declare @endDate as datetime;
 
declare @beginDate1 as datetime;
 
declare @endDate1 as datetime;
 
set @beginDate=cast(@begin1 as datetime);
 
set @endDate=cast(@end1 as datetime);
 
set @beginDate1=cast(@begin2 as datetime);
 
set @endDate1=cast(@end2 as datetime);
 
set @num=0;
 
--如果两个时间差没有交集
   if((@begindate<@begindate1 and @enddate<@begindate1or (@begindate>@enddate1))
    
begin
     
return @num;
    
end;
 
--结束时间大于开始时间
 if(@enddate>=@begindate1 and @begindate<=@begindate1)
   
set @num=datediff(day,@begindate1,@enddate)+1;
 
--时间在另个时间的内e
 if(@beginDate>=@begindate1 and @enddate<=@enddate1)
   
set @num=datediff(day,@begindate,@enddate)+1;
 
--开始时间大于开始时间,结束时间大于结束时间
 if(@begindate>@begindate1 and @enddate>@enddate1 and @begindate<=@enddate1)
   
set @num=datediff(day,@begindate,@enddate1)+1;
  
if(@begindate<=@begindate1 and @enddate>=@enddate1 and @begindate1<= @enddate)
   
set @num=datediff(day,@begindate1,@enddate1)+1;
 
return @num;
end
Go
GO
CREATE proc [dbo].[hjtj](
  
@orgid varchar(32),
  
@begindate varchar(12),
  
@enddate varchar(12)
)
as
 
set nocount on;
 
--办事处临时表
 create table #OrgOffice(
   id 
char(32),
 )
 
--主计划临时表
 create table #mainplan(
   id 
char(32)
 )
 
--类型临时表
 create table #type(
   id 
char(32)
 );
 
create  table #tb(
  typename 
varchar(30),
  totalfee numeric(
18,3),
  months 
varchar(32),
  totalamount numeric(
20,3)
 )
   
--取出该类型的全部子类型(包括自己)
 /*
 insert into #type(id,pid,typeName)
 select id,pid,'终端类型' from selectitem where pid='402881e80caa9192010caa971be5000c'
 union all
 union all
 select id,pid,'非终端类型' from selectitem a where a.pid='402881e80caa9192010caa97560f000e'
 union all
  select id,pid,'地面广告宣传 ' from selectitem a where a.pid='402881e80caa9192010caa9806450010'
 union all
  select id,pid,'周边开发 ' from selectitem a where a.pid='402881e80caa9192010caa9989c80015'
 union all
  select id,id,'总部宣传品 ' from selectitem a where a.id='402881e80caa9192010caa9868b80012'
  
*/

insert into #type(id)
 
select id  from selectitem a where a.pid='402881e80caa9192010caa971be5000c'
 
or a.typeid='402881e80caa9192010caa96a85a000a'
 
or a.pid='402881e80caa9192010caa97560f000e'
 
or a.pid='402881e80caa9192010caa9806450010'
 
or a.pid='402881e80caa9192010caa9989c80015'
 
or a.id='402881e80caa9192010caa9868b80012'
 
--递归查询全部下属机构
;
 
with org(orgid)
 
as
 ( 
    
select a.oid  from  orgunitlink a
   
where a.pid=@orgid
   
union all
    
select c.oid 
   
from orgunitlink c inner join org aa
   
on aa.orgid=c.pid
 )
 
insert into #OrgOffice(id) select a.orgid from org  a union select @orgid
  
--插入机构下的主计划
  insert into #mainplan  
  
select a.id from Mfm_Mainplaninfo a inner join #orgoffice b on b.id=a.orgunitid

declare @beginmonth as datetime;
declare @endmonth as datetime;
declare @beginmonth1 as varchar(10)
declare @endmonth1 as varchar(10)
set @beginmonth=cast(@begindate as datetime);
set @endmonth=cast(@enddate as datetime);
while @beginmonth<=@endmonth
begin
 
  
set @beginmonth1=convert(varchar(10),@beginmonth,120)
  
set @endmonth1=convert(varchar(10),@endmonth,120)
 
if(dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime))>=@enddate)--结束时间在小于当月最后一天
  begin
  
--终端促销日计划金额,日任务量
   insert into #tb(typename,totalfee,months,totalamount)
   
select '终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_terminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
  
and not exists (select 'X' from delobj delobj1_ where Mfm_terminalplan.id=delobj1_.objid)
union all
 
--非终端日计划金额,日任务量
select '非终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Noterminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
  
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from  Mfm_Groundplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
union all
--周边开发
select '周边开发活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Areadevplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
union all
--总部宣传品 
select '总部宣传品活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Groupmaterialplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入 
,
convert(varchar(7),@beginmonth,120
,
cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid 
cross join selectitem at 
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on  m.id = md.mediaplanid 
inner join selectitem a 
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate 
>=@beginmonth1
and md.begindate<=md.enddate 
and md.mark=1)
and a1.mark=1
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname

insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120from 
selectitem a 
where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname 
and t.months=convert(varchar(7),@beginmonth,120))

set  @beginmonth=dateadd(month,3,@endmonth);--设置时间大于结束时间,循环结束
end
else --结束时间在大于当月最后一天
begin
 
--set @beginmonth=@begindate;
 set @endmonth=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天
 if(@endmonth>=@enddate)
  
set @endmonth=@enddate;--假如下一个月最后一天大于结束时间
  set @beginmonth1=convert(varchar(10),@beginmonth,120)
  
set @endmonth1=convert(varchar(10),@endmonth,120)
--运行计算
 insert into #tb(typename,totalfee,months,totalamount)
 
select '终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from  Mfm_terminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
  
and not exists (select 'X' from delobj delobj1_ where Mfm_terminalplan.id=delobj1_.objid)
union all
 
--非终端日计划金额,日任务量
select '非终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Noterminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
  
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Groundplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
union all
--周边开发
select '周边开发活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Areadevplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
union all
--总部宣传品 
select '总部宣传品活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
   
from Mfm_Groupmaterialplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=1 and planstatus=2
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3)) 总投入 
,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getdays(@beginmonth1,@endmonth1,begindate,enddate))  as numeric(22,3))
 
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid 
cross join selectitem at 
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on  m.id = md.mediaplanid 
inner join selectitem a 
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate 
>=@beginmonth1
and md.begindate<=md.enddate 
and md.mark=1)
and a1.mark=1
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
--select @beginmonth--2008-05-01 00:00:00.000
--
select @endmonth
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname 
and t.months=convert(varchar(7),@beginmonth,120))

--计算完毕后,
 set @beginmonth=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);--开始时间为下个月的第一天
 set @endmonth=@enddate 

end
end
 
--总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalfee),'总投入'
from #tb  group by typename

--计算任务调整
insert into #tb(typename,totalfee,months)
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 
inner join selectitem a on a.id=field005
where field011='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250403'
and field005
in ('402881e80caa9192010caa971be5000c' --终端类型
'402881e80caa9192010caa97560f000e' --非终端类型
,'402881e80caa9192010caa9806450010' --地面广告宣传
,'402881e80caa9192010caa9989c80015' --周边开发
,'402881e80caa9192010caa9868b80012'--总部宣传品
)
and field018='1' 
and field002 in (select id from #orgoffice)
group by a.objname
union all
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 
inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a'
and a.id=field025
where 
field011
='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250402'
and field018='1' 
and field002 in (select id from #orgoffice )
group by a.objname--通过媒体类型聚合



--预留额度(机动费)
insert into #tb(typename,totalfee,months)
select ks.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid
inner join 
(
select top 1 * from Mfm_Assessinfo where 
dbo.getdays(
@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid
and (k.pid='402881e80caa9192010caa97560f000e' or k.pid='402881e80caa9192010caa971be5000c'
 
or k.pid='402881e80caa9192010caa9806450010'
 
or k.pid='402881e80caa9192010caa9989c80015')
inner join selectitem ks on ks.id=k.pid
and isactive='1'
group by ks.objname
union all --电视媒体,总部宣传品
select k.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid 
inner join 
(
select top 1 * from Mfm_Assessinfo where 
dbo.getdays(
@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid and (k.typeid='402881e80caa9192010caa96a85a000a'
or k.id='402881e80caa9192010caa9868b80012')
and isactive='1'
group by k.objname

--插入没有数据的机动费,默认设置为0
insert into #tb(typename,totalfee,months)
select a.objname,0,'预留机动费' from selectitem a 
 
where a.id='402881e80caa9192010caa971be5000c'
 
or a.typeid='402881e80caa9192010caa96a85a000a'
 
or a.id='402881e80caa9192010caa97560f000e'
 
or a.id='402881e80caa9192010caa9806450010'
 
or a.id='402881e80caa9192010caa9989c80015'
 
or a.id='402881e80caa9192010caa9868b80012'
 
and not exists(select * from #tb c where c.months='预留机动费' and a.objname=c.typename)

--插入总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalamount),'总任务额度'
from #tb where totalamount>0 
group by typename

--加上预留机动费
update #tb set totalfee=totalfee+c
from #tb  inner join 
(
select a.typename, sum(totalfee) c from #tb  a
where a.months='预留机动费'  
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'
--加上任务调整
update #tb set totalfee=totalfee+c
from #tb  inner join 
(
select a.typename, sum(totalfee) c from #tb  a
where a.months='任务调整'  
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'

declare @sql as varchar(4000);
set @sql='select typename [活动类型]'
select @sql=@sql+',isnull(max(case when months='''+months+''' then totalfee end),0) ['+months+']' from (select distinct months from #tb  ) b
set @sql=@sql+' from #tb t group by  typename order by reverse(typename) desc';
print @sql
exec(@sql)
go

/****** 对象:  StoredProcedure [dbo].[hjtjOrigin1]    脚本日期: 07/09/2008 13:54:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[hjtjold](
  
@orgid varchar(32),
  
@begindate varchar(12),
  
@enddate varchar(12)
)
as
  
set nocount on;
 
--办事处临时表
 create table #OrgOffice(
   id 
char(32),
   company 
char(32)
 )
 
--主计划临时表
 create table #mainplan(
   id 
char(32)
 )
 
--类型临时表
 create table #type(
   id 
char(32)
   
--pid varchar(32),
   --typeName varchar(32) --上级类型的名称,如(非终端,周边开发)
 );
 
create  table #tb(
  typename 
varchar(30),
  totalfee numeric(
18,3),
  months 
varchar(32),
  totalamount numeric(
20,3)
 )
  
declare @regionS as varchar(500);
  
--片区  
  set @regions='402881ba0d6777c5010d68a41ee70037,402881ba0d6777c5010d68a7432f003b,402881ba0d6777c5010d68ab52c30047,402881ba0d6777c5010d68abcc94004a,402881ba0d6777c5010d68ac318b004d'
   
--取出该类型的全部子类型(包括自己)
 /*
 insert into #type(id,pid,typeName)
 select id,pid,'终端类型' from selectitem where pid='402881e80caa9192010caa971be5000c'
 union all
 union all
 select id,pid,'非终端类型' from selectitem a where a.pid='402881e80caa9192010caa97560f000e'
 union all
  select id,pid,'地面广告宣传 ' from selectitem a where a.pid='402881e80caa9192010caa9806450010'
 union all
  select id,pid,'周边开发 ' from selectitem a where a.pid='402881e80caa9192010caa9989c80015'
 union all
  select id,id,'总部宣传品 ' from selectitem a where a.id='402881e80caa9192010caa9868b80012'
  
*/

insert into #type(id)
 
select id  from selectitem a where a.pid='402881e80caa9192010caa971be5000c'
 
or a.typeid='402881e80caa9192010caa96a85a000a'
 
or a.pid='402881e80caa9192010caa97560f000e'
 
or a.pid='402881e80caa9192010caa9806450010'
 
or a.pid='402881e80caa9192010caa9989c80015'
 
or a.id='402881e80caa9192010caa9868b80012'
 
--递归查询全部下属机构
;
 
with org(orgid)
 
as
 ( 
    
select a.oid  from  orgunitlink a
   
where a.pid=@orgid
   
union all
    
select c.oid 
   
from orgunitlink c inner join org aa
   
on aa.orgid=c.pid
 )
 
insert into #OrgOffice(id) select a.orgid from org  a
  
--插入机构下的主计划
  insert into #mainplan  
  
select a.id from Mfm_Mainplaninfo a inner join #orgoffice b on b.id=a.orgunitid

declare @beginmonth as datetime;
declare @endmonth as datetime;
declare @beginmonth1 as varchar(10)
declare @endmonth1 as varchar(10)
set @beginmonth=cast(@begindate as datetime);
set @endmonth=cast(@enddate as datetime);
while @beginmonth<=@endmonth
begin
  
  
set @beginmonth1=convert(varchar(10),@beginmonth,120)
  
set @endmonth1=convert(varchar(10),@endmonth,120)
 
if(dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime))>=@enddate)--结束时间在小于当月最后一天
  begin
  
--终端促销日计划金额,日任务量
   insert into #tb(typename,totalfee,months,totalamount)
   
select '终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_terminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
 
union all
 
--非终端日计划金额,日任务量
select '非终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Noterminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
  
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from  Mfm_Groundplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
union all
--周边开发
select '周边开发活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Areadevplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
union all
--总部宣传品 
select '总部宣传品活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Groupmaterialplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入 
,
convert(varchar(7),@beginmonth,120
,
cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid 
cross join selectitem at 
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on  m.id = md.mediaplanid 
inner join selectitem a 
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate 
>=@beginmonth1
and md.begindate<=md.enddate 
and md.mark=0)
and a1.mark=0
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
--111 select * from #tb
--
插入没有该媒体类型(电视,网络等)数据为0
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120from
 selectitem a 
where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname 
and t.months=convert(varchar(7),@beginmonth,120))

set  @beginmonth=dateadd(month,3,@endmonth);--设置时间大于结束时间,循环结束
end
else --结束时间在大于当月最后一天
begin
 
 
set @endmonth=dateadd(day,-1,cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime));--本月的最后一天
 if(@endmonth>=@enddate)
  
set @endmonth=@enddate;--假如下一个月最后一天大于结束时间
  set @beginmonth1=convert(varchar(10),@beginmonth,120)
  
set @endmonth1=convert(varchar(10),@endmonth,120)
--运行计算
 insert into #tb(typename,totalfee,months,totalamount)
 
select '终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from  Mfm_terminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
union all
 
--非终端日计划金额,日任务量
select '非终端促销活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Noterminalplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
  
union all
--地面广告宣传日计划金额,日任务量
select '地面宣传活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Groundplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
union all
--周边开发
select '周边开发活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Areadevplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
union all
--总部宣传品 
select '总部宣传品活动'cast(sum(feeamount/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入
   ,
convert(varchar(7),@beginmonth,120
   ,
cast(sum(feeamount*inoutratio/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
   
from Mfm_Groupmaterialplan  
   
inner join Mfm_Mainplaninfo a on mainplanid=a.id
   
inner join #orgoffice b on b.id=a.orgunitid
   
inner join #type on acttype=#type.id  
   
inner join orgunit on orgunit.id=b.id 
   
inner join selectitem k on k.id=#type.id 
   
where
        begindate
<=@endmonth1
        
and enddate>=@beginmonth1
        
and begindate<=enddate
        
and mark=0 
--媒体类型
union all
select at.objname, cast(sum(isnull(a1.feeamount,0)/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3)) 总投入 
,
convert(varchar(7),@beginmonth,120)
   ,
cast(sum(feeamount*a1.inout/dbo.getday(begindate,enddate)*dbo.getbetweenDays(@beginmonth1,@endmonth1,@begindate,@enddate,begindate,enddate))  as numeric(22,3))
 
from mfm_mediadetailplaninfo a1 inner join mfm_mediaplaninfo mm
on mm.id = a1.mediaplanid 
cross join selectitem at 
where a1.mediaplanid in(
select distinct m.id from mfm_mediaplaninfo m inner join mfm_mediadetailplaninfo md
on  m.id = md.mediaplanid 
inner join selectitem a 
on a.typeid='402881e80caa9192010caa96a85a000a' and a.id=m.mediatypeid
inner join #mainplan c
on m.mainplanid =c.id
where md.begindate <= @endmonth1 and
md.enddate 
>=@beginmonth1
and md.begindate<=md.enddate 
and md.mark=0)
and a1.mark=0
and at.id=mm.mediatypeid
and at.typeid='402881e80caa9192010caa96a85a000a'
group by at.objname
--select * from #tb
insert into #tb(typename,totalfee,months)
select a.objname,0,convert(varchar(7),@beginmonth,120from selectitem a where a.typeid='402881e80caa9192010caa96a85a000a' and
not exists (select 1 from #tb t where t.typename=a.objname 
and t.months=convert(varchar(7),@beginmonth,120))

--计算完毕后,开始时间为下个月的第一天
 set @beginmonth=cast(convert(varchar(7),dateadd(month,1,@beginmonth),120)+'-01' as datetime);
 
set @endmonth=@enddate --结束时间为默认结束时间

end
end

--计算总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalfee),'总投入'
from #tb  group by typename

--任务调整
insert into #tb(typename,totalfee,months)
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 
inner join selectitem a on a.id=field005
where field011='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250403'
and field005
in ('402881e80caa9192010caa971be5000c' --终端类型
'402881e80caa9192010caa97560f000e' --非终端类型
,'402881e80caa9192010caa9806450010' --地面广告宣传
,'402881e80caa9192010caa9989c80015' --周边开发
,'402881e80caa9192010caa9868b80012'--总部宣传品
)
and field018='1' 
and field002 in (select id from #orgoffice)
and field027 is null --为null为原始计划
group by a.objname
union all
select a.objname,sum(field008),'任务调整' from ufg0z3a51168936462406 
inner join selectitem a on a.typeid='402881e80caa9192010caa96a85a000a'
and a.id=field025
where 
field011
='4028827015f58e7901161d0b63236364'
and field012='40288141103870fb0110487902250402'
and field018='1' 
and field002 in (select id from #orgoffice )
and field027 is null --为null为原始计划
group by a.objname--通过媒体类型聚合

--预留额度(机动费)
insert into #tb(typename,totalfee,months)
select ks.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid
inner join 
(
select top 1 * from Mfm_Assessinfo where 
dbo.getdays(
@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid
and (k.pid='402881e80caa9192010caa97560f000e' or k.pid='402881e80caa9192010caa971be5000c'
 
or k.pid='402881e80caa9192010caa9806450010'
 
or k.pid='402881e80caa9192010caa9989c80015')
inner join selectitem ks on ks.id=k.pid
and isactive='0'
group by ks.objname
union all --电视媒体,总部宣传品
select k.objname,sum(a.feeamount*cast(a.col1 as numeric(10,2))),'预留机动费' from Mfm_Budget a
inner join #mainplan b on b.id=a.mainplanid 
inner join 
(
select top 1 * from Mfm_Assessinfo where 
dbo.getdays(
@begindate,@enddate,begindate,enddate)>0) c on assessinfoid=c.id
inner join selectitem k on k.id=a.acttypeid and (k.typeid='402881e80caa9192010caa96a85a000a'
or k.id='402881e80caa9192010caa9868b80012')
and isactive='0'
group by k.objname

--插入没有数据的机动费,默认设置为0
insert into #tb(typename,totalfee,months)
select a.objname,0,'预留机动费' from selectitem a 
 
where a.id='402881e80caa9192010caa971be5000c'
 
or a.typeid='402881e80caa9192010caa96a85a000a'
 
or a.id='402881e80caa9192010caa97560f000e'
 
or a.id='402881e80caa9192010caa9806450010'
 
or a.id='402881e80caa9192010caa9989c80015'
 
or a.id='402881e80caa9192010caa9868b80012'
 
and not exists(select * from #tb c where c.months='预留机动费' and a.objname=c.typename)

--计算总任务额度(预留机动费+任务调整+总投入)
--
插入总投入
insert into #tb(typename,totalfee,months)
select typename,sum(totalamount),'总任务额度'
from #tb where totalamount>0 
group by typename

--加上预留机动费
update #tb set totalfee=totalfee+c
from #tb  inner join 
(
select a.typename, sum(totalfee) c from #tb  a
where a.months='预留机动费'  
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'
--加上任务调整
update #tb set totalfee=totalfee+c
from #tb  inner join 
(
select a.typename, sum(totalfee) c from #tb  a
where a.months='任务调整'  
group by a.typename) c
on c.typename=#tb.typename
where #tb.months='总任务额度'

--输出数据
declare @sql as varchar(4000);
set @sql='select typename [活动类型]'
select @sql=@sql+',isnull(max(case when months='''+months+''' then totalfee end),0) ['+months+']' from (select distinct months from #tb  ) b
set @sql=@sql+' from #tb t group by  typename order by reverse(typename) desc';
print @sql
exec(@sql)
go


posted @ 2008-07-15 13:44  zping  阅读(1594)  评论(1编辑  收藏  举报