经典存储过程计算报表

     用单一存储过程计算报表，替换以前用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<@begindate1) or (@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,120) from 
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,120) from 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,120) from
 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,120) from 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  阅读(1610)  评论(1)  编辑  收藏  举报