存储过程构造数据表

ALTER PROCEDURE CustomerService_SelectTable_SearchCondition_OfficeUsage
(
 @CustomerAccountID int = null,
 @Status varchar(50) = null,
 @ServiceType varchar(50) = null,
 @SpecifiedServiceID int = null,
 @LocationID int = null,
 @PageIndex int=null,
 @PageSize int=null,
    @TotalNumber int output
)

AS
SET NOCOUNT ON

DECLARE @PageLowerBound int
DECLARE @PageUpperBound int

SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageSize * (@PageIndex+1)
if object_id('tempdb..#temp1') is not null
begin
 drop table tempdb.#temp1
end
------------------------------First Step GetDifferentDate
CREATE TABLE #temp1
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 CustomerServiceID int,
 CustomerID int,
 MailingAddressCompanyName nvarchar(100),
 MonthlyCreditHours int,
 FDCur datetime,--Different Years between Today and SetupTime
 SetupTime datetime,
    CancelTime datetime 
)
insert into #temp1
select CustomerServiceID,CustomerID ,MailingAddressCompanyName,MonthlyCreditHours,
       DATEADD(month,DATEDIFF(month,SetupTime,GETDATE()),SetupTime) as FDCur,SetupTime,CancelTime
from vUsageOfficeCustomer
where LocationID=@LocationID
      --And (CustomerID = @CustomerAccountID or @CustomerAccountID is null)
      And (Status = @Status or @Status is null)
      And (SetupTime is not null)
      And (CancelTime is null)

if object_id('tempdb..#addtionalTable1') is not null
begin
 drop table tempdb.#addtionalTable1
end
CREATE TABLE #addtionalTable1
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 CustomerServiceID int,
 CustomerID int,
 MailingAddressCompanyName nvarchar(100),
 MonthlyCreditHours int,
 Diff int,--Different Years between Today and SetupTime 
 FDCur datetime,
 SetupTime datetime,
    CancelTime datetime             
)
insert into #addtionalTable1
select CustomerServiceID,CustomerID ,MailingAddressCompanyName,MonthlyCreditHours,day(GetDate())-day(FDCur)as Diff,FDCur,SetupTime,CancelTime
from #temp1

if object_id('tempdb..#temp2') is not null
begin
 drop table tempdb.#temp2
end
-----------------------------Second Step Build FromDate
CREATE TABLE #temp2
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 CustomerServiceID int,
 CustomerID int,
 MailingAddressCompanyName nvarchar(100),
 MonthlyCreditHours int,
 FromDate datetime,
 ActiviationTime datetime              
)
Insert Into #temp2
select CustomerServiceID,CustomerID ,MailingAddressCompanyName ,MonthlyCreditHours,
Convert(char,case when Diff<0 then DateAdd(month,-1,FDCur) ElSE FDCur End,101) as FromDate,
SetupTime as ActiviationTime
from #addtionalTable1

 
if object_id('tempdb..#temp3') is not null
begin
 drop table tempdb.#temp3
end
-----------------------------Third Step Build UsageOffice Used Credit Hours
CREATE TABLE #temp3
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 ReservationID int,
    CustomerServiceID int,
 UsedCreditHours decimal(18,2),
    FromDate datetime

)
Insert Into #temp3
SELECT cou.ReservationID,cou.CustomerServiceID,cou.UsedCreditHours,t2.FromDate
From  #temp2 t2 left JOIN CustomerOfficeUsage cou  ON  cou.CustomerServiceID = t2.CustomerServiceID
Where cou.CreationTime>=t2.FromDate AND cou.CreationTime<=GetDate()

declare @TempValue decimal;
set @TempValue=0
Insert Into #temp3
Select  0 as ReservationID,t2.CustomerServiceID, @TempValue as UsedCreditHours,t2.FromDate
From #temp2 t2
where t2.CustomerServiceID not in(select cou.CustomerServiceID
         From CustomerOfficeUsage cou
         Where cou.CreationTime<=GetDate() and cou.LocationID=@LocationID)

-----------------------------Fourth Step GetSUM UsageOffice Used Credit Hours

if object_id('tempdb..#temp4') is not null
begin
 drop table tempdb.#temp4
end
CREATE TABLE #temp4
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 CustomerServiceID int,
 UsedCreditHours decimal(18,2) 
)
insert into #temp4
select  CustomerServiceID,sum(UsedCreditHours) as UsedCreditHours
from #temp3
group by CustomerServiceID

if object_id('tempdb..#temp5') is not null
begin
 drop table tempdb.#temp5
end
-----------------------------Fifth Step Get  A Table that is
CREATE TABLE #temp5
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 CustomerServiceID int,
 CustomerID int,
 MailingAddressCompanyName nvarchar(100),
                UsedCreditHours decimal(18,2),
 MonthlyCreditHours int,
 FromDate datetime,
 ActiviationTime datetime
                
)
insert into #temp5
select t2.CustomerServiceID,t2.CustomerID ,t2.MailingAddressCompanyName ,
         (case when t4.UsedCreditHours is null then 0 else t4.UsedCreditHours end) as UsedCreditHours,t2.MonthlyCreditHours,t2.FromDate, t2.ActiviationTime
from #temp4 t4 right outer JOIN #temp2 t2 on t4.CustomerServiceID =t2.CustomerServiceID

if object_id('tempdb..#AddTemp1') is not null
begin
 drop table tempdb.#AddTemp1
end

CREATE TABLE #AddTemp1
(
 RecordId int IDENTITY (1, 1) NOT NULL,
                CustomerServiceID int,
                 ReservationID int,
 IncidentalServiceID int,
 UsedAmount decimal(18,2),
                CheckOutTime datetime
 )
insert into #AddTemp1
SELECT       #temp3.CustomerServiceID,ReservationIncidentalService.ReservationID, ReservationIncidentalService.LocationIncidentalServiceID, ReservationIncidentalService.AmountUsed,OfficeFacilityReservation.CheckOutTime
FROM         #temp3 inner   join
                        OfficeFacilityReservation INNER JOIN
                      ReservationIncidentalService ON OfficeFacilityReservation.ReservationID = ReservationIncidentalService.ReservationID
                       on  #temp3.ReservationID=ReservationIncidentalService.ReservationID
WHERE        (OfficeFacilityReservation.WithCredit = 1) and ((OfficeFacilityReservation.CheckOutTime >=#temp3.FromDate)  and(OfficeFacilityReservation.CheckOutTime<=GetDate()))

if object_id('tempdb..#temp6') is not null
begin
 drop table tempdb.#temp6
end
-----------------------------Fifth Step Select CategoryID,CategoryNameKey by IncidentalServiceID
CREATE TABLE #temp6
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 IncidentalServiceID int,
 LocationID int ,
               CategoryID int,
               CategoryNameKey varchar(50)
)
insert into #temp6
SELECT       lis .IncidentalServiceID, lis .LocationID, lisr .CategoryID, lisc.CategoryNameKey
FROM            LocationIncidentalService  lis LEFT OUTER JOIN
                         LocationIncidentalServiceCategory lisc INNER JOIN
                         LocationIncidentalServiceRelationship lisr ON lisc.CategoryID = lisr.CategoryID ON lis.ServiceID = lisr.ServiceID                        
WHERE        (lis.LocationID = @LocationID) and (CategoryNameKey is not null) and (lisr.UsageAllowedWithoutReservation=1)

insert into #temp6 values(null,@LocationID,5,'Other')

if object_id('tempdb..#temp7') is not null
begin
 drop table tempdb.#temp7
end
-----------------------------Sixth Step Joined IncidentalServiceCharge Table and #temp6
CREATE TABLE #temp7
(
 RecordId int IDENTITY (1, 1) NOT NULL,
                CustomerServiceID int,
 IncidentalServiceID int,
 LocationID int,
 CategoryID int,
    CategoryNameKey varchar(50),
 UsedAmount decimal(18,2),
 FinalCharge decimal(18,2),
 CreationTime datetime  
)
insert into #temp7
select lisc.CustomerServiceID,t6.IncidentalServiceID,t6.LocationID,t6.CategoryID,t6.CategoryNameKey,lisc.UsedAmount,lisc.FinalCharge,lisc.CreationTime
from #temp6 t6  right join LocationIncidentalServiceCharge lisc on t6.IncidentalServiceID=lisc.LocationIncidentalServiceID

 

declare @withCreditResult int;
select @withCreditResult =count(at1.RecordId )
from #AddTemp1 at1

if @withCreditResult>0
begin
insert into #temp7
select distinct t7.CustomerServiceID,t7.IncidentalServiceID,t7.LocationID,t7.CategoryID,t7.CategoryNameKey,at1.UsedAmount,
           case  when t7.FinalCharge<>0 then 0 else 0 end,at1.CheckOutTime
from #temp7 t7 inner join #AddTemp1 at1 on  t7.IncidentalServiceID=at1.IncidentalServiceID and t7.CustomerServiceID =at1.CustomerServiceID
end

 

declare @count as int;
select @count=count( *) from LocationIncidentalServiceCharge
where LocationID=@LocationID and LocationIncidentalServiceID is null and CustomerServiceID in (select CustomerServiceID from #temp5)
if @count>0
begin
  insert into #temp7
  select lisc.CustomerServiceID,t6.IncidentalServiceID,t6.LocationID,t6.CategoryID,t6.CategoryNameKey,lisc.UsedAmount,lisc.FinalCharge,lisc.CreationTime
  from #temp6 t6  right join LocationIncidentalServiceCharge lisc on t6.LocationID=lisc.LocationID
   where lisc.LocationID=@LocationID and t6.CategoryNameKey='Other' AND t6.IncidentalServiceID IS NULL and CustomerServiceID in (select CustomerServiceID from #temp5)
end

if object_id('tempdb..#temp8') is not null
begin
 drop table tempdb.#temp8
end
-----------------------------Seventh Step Build  a Table About Incidental Service Charge Type
CREATE TABLE #temp8
(
 RecordId int IDENTITY (1, 1) NOT NULL,
                CustomerServiceID int,
 IncidentalServiceID int,
 LocationID int,
                CommunicationsType decimal(18,2),
 AssistantType decimal(18,2),               
                CopyType decimal(18,2),
                PrintType decimal(18,2),
                OtherType decimal(18,2),               
             CreationTime datetime   
)
insert into #temp8
select  CustomerServiceID,IncidentalServiceID,LocationID,
         (case when CategoryNameKey ='Communications' then UsedAmount else 0 end) as CommunicationsType ,
         (case when CategoryNameKey ='ProfessionalAssistant' then UsedAmount else 0 end) as AssistantType,        
         (case when CategoryNameKey ='Copy' then UsedAmount else 0 end) as CopyType,
         (case when CategoryNameKey ='Print' then UsedAmount else 0 end) as PrintType,
         (case when CategoryNameKey ='Other' then FinalCharge else 0 end) as OtherType ,
         Convert(char,CreationTime,101)as CreationTime
from #temp7
--where IncidentalServiceID is not null and CategoryID is not null

--select * from #temp8

if object_id('tempdb..#temp9') is not null
begin
 drop table tempdb.#temp9
end
-----------------------------Eighth Step Join IncidentalServiceChargeType and #temp5

CREATE TABLE #temp9
(
 RecordId int IDENTITY (1, 1) NOT NULL,
                CustomerServiceID int,
 IncidentalServiceID int,
                CommunicationsType  decimal(18,2),
 AssistantType decimal(18,2),               
                CopyType decimal(18,2),
                PrintType decimal(18,2),
               OtherType  decimal(18,2)              
)
insert into #temp9
select  t8.CustomerServiceID,t8.IncidentalServiceID,t8.CommunicationsType,t8.AssistantType,t8.CopyType,t8.PrintType,t8.OtherType
from #temp5 t5 right outer join #temp8 t8 on t5.CustomerServiceID=t8.CustomerServiceID
where t8.CreationTime>=t5.FromDate and t8.CreationTime<=GetDate();

insert into #temp9
select  t5.CustomerServiceID,null as IncidentalServiceID,@TempValue as CommunicationsType,@TempValue as AssistantType,@TempValue as CopyType,@TempValue as PrintType,@TempValue as OtherType
from #temp5 t5
where t5.CustomerServiceID not in (select t8.CustomerServiceID
          From #temp8 t8
          where t8.CreationTime>=t5.FromDate and t8.CreationTime<=GetDate())
          

if object_id('tempdb..#temp10') is not null
begin
 drop table tempdb.#temp10
end
CREATE TABLE #temp10
(
 RecordId int IDENTITY (1, 1) NOT NULL,
               CustomerServiceID int,
                --IncidentalServiceID int,
                CommunicationsType  decimal(18,2),
 AssistantType decimal(18,2),               
                CopyType decimal(18,2),
                PrintType decimal(18,2),
               OtherType  decimal(18,2)            
)
insert into #temp10
select CustomerServiceID,sum(CommunicationsType ) as CommunicationsType ,
          sum(AssistantType) as AssistantType, sum( CopyType) as CopyType,sum(PrintType) as PrintType,sum(OtherType ) as OtherType
from #temp9
group by CustomerServiceID --,IncidentalServiceID

 

if object_id('tempdb..#temp11') is not null
begin
 drop table tempdb.#temp11
end
-----------------------------Last Step Build Result Table
CREATE TABLE #temp11
(
 RecordId int IDENTITY (1, 1) NOT NULL,
 CustomerServiceID int,
               -- IncidentalServiceID int,
 CustomerID int,
 MailingAddressCompanyName nvarchar(100),
                UsedCreditHours decimal(18,2),
 MonthlyCreditHours int,
                CommunicationsType  decimal(18,2),
                AssistantType decimal(18,2),
                CopyType decimal(18,2),
                PrintType decimal(18,2),
                OtherType  decimal(18,2),  
 FromDate datetime,
 ActiviationTime datetime 
  
 
)
insert into #temp11
select  t5.CustomerServiceID,t5.CUstomerID,t5.MailingAddressCompanyName ,
t5.UsedCreditHours ,
t5.MonthlyCreditHours ,
(case when t10.CommunicationsType  is null then 0 else t10.CommunicationsType  end) as CommunicationsType ,
(case when t10.AssistantType is null then 0 else t10.AssistantType end) as AssistantType ,
(case when t10.CopyType is null then 0 else t10.CopyType end) as CopyType,
(case when t10.PrintType is null then 0 else  t10.PrintType end) as PrintType,
(case when t10.OtherType  is null then 0 else t10.OtherType  end) as OtherType,
            t5.FromDate ,t5.ActiviationTime
from #temp5 t5 right outer join #temp10 t10 on t5.CustomerServiceID=t10.CustomerServiceID

 

select @TotalNumber=count(RecordID)from #temp11

select CustomerServiceID,CustomerID,MailingAddressCompanyName,UsedCreditHours,MonthlyCreditHours,CommunicationsType,AssistantType, CopyType,PrintType,OtherType,
       Convert(char,FromDate,101) as FromDate,
       Convert(char,ActiviationTime,101) as ActiviationTime
from #temp11
WHERE (RecordId > @PageLowerBound)
   AND (RecordId <= @PageUpperBound)

RETURN