CREATE PROCEDURE [dbo].[SelectCustomerOrderBookTopList] 
  @CustomerTypeNumber SMALLINT, 
  @FromDate VARCHAR(8), 
  @ToDate VARCHAR(8), 
  @Segments VARCHAR(MAX), 
  @ReservationStatuses VARCHAR(128), 
  @TopList INT, 
  @FilterSQL VARCHAR(2000) = '', 
  @OrderBySQL VARCHAR(128) = '', 
  @Hotels VARCHAR(MAX), 
  @IncludeBonusPoints BIT, 
  @IncludeDownLinks BIT = 1, 
  @Corrections BIT, 
  @IncludeExpiredBonusPoints BIT = 0, 
  @VATParam MONEY = 1,
  @AmountsInThousands BIT = 0

/*
  Changes:
    SG 20161101: CR 113756: Segments and Hotels -> VarChar(MAX)
    KJ: Req #2728: Added amount in thousands.
    OF 20110323: Ext: BonusPointsEarned and BonusPointsSpent extracted.
    OF 20110317: Bug: Calculation og spent bonus points changed. Only when minus bonuspoints and TotalPrice is 0, are considered.
    VD 20110317: Ext: Use of VAT Factor implemented
	OF 20110310: Ext: IncludeExpiredBonusPoints option included.
	OF 20101216: Bug: Disable use of Segments when ForceOnSegments (Hotelsetup) is false.
*/

AS
BEGIN

	CREATE TABLE #Segments( [Segment] [int] not null)
    if @Segments = '' 
		Insert into #Segments select NUMBER from dbo.Segments
	else
		Insert into #Segments select Value from dbo.SplitNumberList(@Segments)
	CREATE TABLE #ReservationStatuses( [ReservationStatus] [int] not null)
	if @ReservationStatuses = ''
		Insert into #ReservationStatuses select Value from dbo.SplitNumberList('-1,0,1,2,3,4,5,6,7,8') -- all reservation types
	else
		Insert into #ReservationStatuses select Value from dbo.SplitNumberList(@ReservationStatuses)
	CREATE TABLE #CustomerLinks( [CustomerNumber] [int] not null, [DownLinks] [int] not null)

	DECLARE @CustomerNumber int
	DECLARE @TurnOverRooms money
	DECLARE @NumberOfNightRooms int
	DECLARE @NumberOfPaxRooms int
	DECLARE @NumberOfStaysRooms int
	DECLARE @NumberOfCXLRooms int
	DECLARE @NumberOfNoShowRooms int
	DECLARE @TurnOverDaymeetings money
	DECLARE @NumberOfPaxDaymeetings int
	DECLARE @NumberOfStaysDaymeetings int
	DECLARE @TurnOverHalls money
	DECLARE @NumberOfHalls int
	DECLARE @NumberOfPaxHalls int
	DECLARE @TurnOverRestaurant money
	DECLARE @TurnOverOther money
	DECLARE @TurnOverTotal money
	DECLARE @BonusPoints money
	DECLARE @CustomerTypesStr varchar(20)
	DECLARE @BaseFilterSQL varchar(50)
	DECLARE @CorrectionStr varchar(3)
	DECLARE @UseSegments bit

	SET @CorrectionStr = '0,1'
	IF @Corrections = 0
	  SET @CorrectionStr = '0'
	  
	SELECT @UseSegments = KeyValue FROM HotelSetup (NOLOCK) WHERE Upper(SearchKey) = Upper('ForceOnSegment')

	CREATE TABLE #OrderbookSum(
		[CustomerNumber] [int] NOT NULL,
		[TurnOverRooms] [money] NULL,
		[NumberOfNightRooms] [int] NULL,
		[NumberOfPaxRooms] [int] NULL,
		[NumberOfStaysRooms] [int] NULL,
		[NumberOfCXLRooms] [int] NULL,
		[NumberOfNoShowRooms] [int] NULL,
		[TurnOverDaymeetings] [money] NULL,
		[NumberOfPaxDaymeetings] [int] NULL,
		[NumberOfStaysDaymeetings] [int] NULL,
		[TurnOverHalls] [money] NULL,
		[NumberOfHalls] [int] NULL,
		[NumberOfPaxHalls] [int] NULL,
		[TurnOverRestaurant] [money] NULL,
		[TurnOverOther] [money] NULL,
		[TurnOverTotal] [money] NULL,
		[BonusPointsEarned] [money] NULL,
		[BonusPointsSpent] [money] NULL
	)
	-- Create temporary table
	SELECT *
	INTO #OrderbookSum_Temp
	FROM #OrderbookSum

	-- *****  Find all downlinks and include the customer itself in table #CustomLinks *****
	SET @CustomerTypesStr = '4,5,7' -- Company Corp, Agency Corp, SalesChannel
	if @CustomerTypeNumber <> 9 -- Not defined. 9 means all customers
	begin
		SET @CustomerTypesStr = cast(@CustomerTypeNumber as varchar(10))
	end
	
	IF OBJECT_ID('tempdb..#MarketingSelectedCustomers') IS NOT NULL  -- Temporary table made from code to parse selected customers.
	BEGIN
		IF @IncludeDownLinks = 1
		BEGIN
			INSERT INTO #CustomerLinks
				Select CustomerLinks.CustomerNumber, CustomerLinks.CustomerLinkNumber from CustomerLinks WITH (NOLOCK) 
				inner join Customer on Customer.CustomerNumber = CustomerLinks.CustomerNumber and Customer.MarkAsDeleted = 0 and Customer.CustomerTypeNumber in (4,5,7)
				where CustomerLinks.LinkTypeNumber = 0 and CustomerLinks.CustomerNumber in (Select Distinct Number From #MarketingSelectedCustomers)
				UNION ALL
				Select CustomerLinks.CustomerLinkNumber, CustomerLinks.CustomerNumber from CustomerLinks WITH (NOLOCK)
				inner join Customer on Customer.CustomerNumber = CustomerLinks.CustomerLinkNumber and Customer.MarkAsDeleted = 0 and Customer.CustomerTypeNumber in (4,5,7)
				where CustomerLinks.LinkTypeNumber = 0 and CustomerLinks.CustomerLinkNumber in (Select Distinct Number From #MarketingSelectedCustomers)
				order by CustomerLinks.CustomerNumber, CustomerLinks.CustomerLinkNumber 
		END
		INSERT INTO #CustomerLinks
			SELECT DISTINCT Number, Number FROM #MarketingSelectedCustomers
	END
	ELSE
    BEGIN
		IF @IncludeDownLinks = 1
		BEGIN
			if @CustomerTypeNumber = 4 or @CustomerTypeNumber = 5 or @CustomerTypeNumber = 7 or @CustomerTypeNumber = 9
			BEGIN
				INSERT INTO #CustomerLinks
					Select CustomerLinks.CustomerNumber, CustomerLinks.CustomerLinkNumber from CustomerLinks WITH (NOLOCK) 
					inner join Customer on Customer.CustomerNumber = CustomerLinks.CustomerNumber and Customer.CustomerTypeNumber in (select * from dbo.SplitNumberList(@CustomerTypesStr)) and Customer.MarkAsDeleted = 0
					where CustomerLinks.LinkTypeNumber = 0
					UNION ALL
					Select CustomerLinks.CustomerLinkNumber, CustomerLinks.CustomerNumber from CustomerLinks WITH (NOLOCK)
					inner join Customer on Customer.CustomerNumber = CustomerLinks.CustomerLinkNumber and Customer.CustomerTypeNumber in (select * from dbo.SplitNumberList(@CustomerTypesStr)) and Customer.MarkAsDeleted = 0
					where CustomerLinks.LinkTypeNumber = 0
					order by CustomerLinks.CustomerNumber, CustomerLinks.CustomerLinkNumber 

			END
		END

		INSERT INTO #CustomerLinks
			SELECT CustomerNumber, CustomerNumber From Customer where CustomerTypeNumber = @CustomerTypeNumber or @CustomerTypeNumber = 9
	END
    -- ************************

	-- Summarize for customers
	Insert into #OrderbookSum_Temp 
		Select distinct B.CustomerNumber, Sum(TurnOverRooms) as TurnoverRooms, Sum(NumberOfNightRooms) as NumberOfNightRooms , Sum(NumberOfPaxRooms) as NumberOfPaxRooms, Sum(NumberOfStaysRooms) as NumberOfStaysRooms,
			Sum(NumberOfCXLRooms) as NumberOfCXLRooms, Sum(NumberOfNoShowRooms) as NumberOfNoShowRooms, Sum(TurnOverDaymeetings) as TurnOverDaymeetings, Sum(NumberOfPaxDaymeetings) as NumberOfPaxDaymeetings, Sum(NumberOfStaysDaymeetings) as NumberOfStaysDaymeetings,
			Sum(TurnOverHalls) as TurnOverHalls, Sum(NumberOfHalls) as NumberOfHalls, Sum(NumberOfPaxHalls) as NumberOfPaxHalls, Sum(TurnOverRestaurant) as TurnOverRestaurant, Sum(TurnOverOther) as TurnOverOther, 
			Sum(TurnOverRooms + TurnOverDaymeetings + TurnOverRestaurant + TurnOverHalls + TurnOverOther) as TurnOverTotal, 
			NULL as BonusPointsEarned, NULL as BonusPointsSpent
		from CustomerOrdreBook A WITH (nolock)
			inner join #CustomerLinks B on B.DownLinks = A.CustomerNumber
		where 
			--A.Segmentnumber in (SELECT Segment FROM #Segments)
			(A.Segmentnumber in (SELECT Segment FROM #Segments) OR @UseSegments = 0)
			AND A.ReservationStatus IN (select ReservationStatus from #ReservationStatuses)
			AND isnull(A.Correction, 0) IN (SELECT value FROM SplitNumberList(@CorrectionStr)) 
		--	AND A.Correction IN (0)
		--	AND (Convert(char(8), DateForValue, 112) >=  @FromDate AND Convert(char(8), DateForValue, 112) <= @ToDate)
            AND ((@FromDate = '') OR (CONVERT(char(8), DateForValue, 112) >= @FromDate))
			AND ((@ToDate = '') OR (CONVERT(char(8), DateForValue, 112) <= @ToDate))
            AND ((@Hotels = '') or A.HotelCustomerNumber in (select * from dbo.SplitNumberList(@Hotels)))
		group by B.CustomerNumber

	-- Summarize for customers where they are registered as contactpersons
	Insert into #OrderbookSum_Temp 
		Select distinct B.CustomerNumber, Sum(TurnOverRooms) as TurnoverRooms, Sum(NumberOfNightRooms) as NumberOfNightRooms , Sum(NumberOfPaxRooms) as NumberOfPaxRooms, Sum(NumberOfStaysRooms) as NumberOfStaysRooms,
			Sum(NumberOfCXLRooms) as NumberOfCXLRooms, Sum(NumberOfNoShowRooms) as NumberOfNoShowRooms, Sum(TurnOverDaymeetings) as TurnOverDaymeetings, Sum(NumberOfPaxDaymeetings) as NumberOfPaxDaymeetings, Sum(NumberOfStaysDaymeetings) as NumberOfStaysDaymeetings,
			Sum(TurnOverHalls) as TurnOverHalls, Sum(NumberOfHalls) as NumberOfHalls, Sum(NumberOfPaxHalls) as NumberOfPaxHalls, Sum(TurnOverRestaurant) as TurnOverRestaurant, Sum(TurnOverOther) as TurnOverOther, 
			Sum(TurnOverRooms + TurnOverDaymeetings + TurnOverRestaurant + TurnOverHalls + TurnOverOther) as TurnOverTotal, 
			NULL as BonusPointsEarned, NULL as BonusPointsSpent
		from CustomerOrdreBook A WITH (nolock)
			inner join #CustomerLinks B on B.DownLinks = A.ContactPersonCustomerNumber 
		where 
			--A.Segmentnumber in (SELECT Segment FROM #Segments)
			(A.Segmentnumber in (SELECT Segment FROM #Segments) OR @UseSegments = 0)
			AND A.ReservationStatus IN (select ReservationStatus from #ReservationStatuses)
			AND isnull(A.Correction, 0) IN (SELECT value FROM SplitNumberList(@CorrectionStr)) 
			--AND A.Correction IN (0)
			--AND (Convert(char(8), DateForValue, 112) >=  @FromDate AND Convert(char(8), DateForValue, 112) <= @ToDate)
            AND ((@FromDate = '') OR (CONVERT(char(8), DateForValue, 112) >= @FromDate))
			AND ((@ToDate = '') OR (CONVERT(char(8), DateForValue, 112) <= @ToDate))
            AND ((@Hotels = '') or A.HotelCustomerNumber in (select * from dbo.SplitNumberList(@Hotels)))
		group by B.CustomerNumber

	-- Summarize for bonus points if choosen
    if @IncludeBonusPoints = 1
	begin
		Insert into #OrderbookSum_Temp 
			Select distinct B.CustomerNumber, Sum(0) as TurnoverRooms, Sum(0) as NumberOfNightRooms , Sum(0) as NumberOfPaxRooms, Sum(0) as NumberOfStaysRooms,
				Sum(0) as NumberOfCXLRooms, Sum(0) as NumberOfNoShowRooms, Sum(0) as TurnOverDaymeetings, Sum(0) as NumberOfPaxDaymeetings, Sum(0) as NumberOfStaysDaymeetings,
				Sum(0) as TurnOverHalls, Sum(0) as NumberOfHalls, Sum(0) as NumberOfPaxHalls, Sum(0) as TurnOverRestaurant, Sum(0) as TurnOverOther, 
				Sum(0) as TurnOverTotal, 
				Sum(B.BonusPointsEarned), Sum(B.BonusPointsSpent)
			from #CustomerLinks A (nolock)
				inner join dbo.CustomerOrderBookBonusPoints('', @FromDate, @ToDate, @Segments, @Hotels, @IncludeExpiredBonusPoints) B on A.DownLinks = B.CustomerNumber 
			group by B.CustomerNumber
	end

    -- Summarize all
    -- Summarize all
  IF @AmountsInThousands = 1
  BEGIN
	  INSERT INTO #OrderbookSum 
		  SELECT 
		    DISTINCT CustomerNumber, 
		    ROUND(SUM(TurnOverRooms * @VATParam), -3)/1000 AS TurnOverRooms, 
		    SUM(NumberOfNightRooms) AS NumberOfNightRooms , 
		    SUM(NumberOfPaxRooms) AS NumberOfPaxRooms, 
		    SUM(NumberOfStaysRooms) AS NumberOfStaysRooms,
		    SUM(NumberOfCXLRooms) AS NumberOfCXLRooms, 
		    SUM(NumberOfNoShowRooms) AS NumberOfNoShowRooms, 
        ROUND(SUM(TurnOverDaymeetings * @VATParam), -3)/1000 AS TurnOverDaymeetings, 
		    SUM(NumberOfPaxDaymeetings) AS NumberOfPaxDaymeetings, 
	      SUM(NumberOfStaysDaymeetings) AS NumberOfStaysDaymeetings,
		    ROUND(SUM(TurnOverHalls * @VATParam), -3)/1000 AS TurnOverHalls, 
	      SUM(NumberOfHalls) AS NumberOfHalls, 
		    SUM(NumberOfPaxHalls) AS NumberOfPaxHalls, 
		    ROUND(SUM(TurnOverRestaurant * @VATParam), -3)/1000 AS TurnOverRestaurant, 
		    ROUND(SUM(TurnOverOther * @VATParam), -3)/1000 AS TurnOverOther, 
		    ROUND(SUM(TurnOverRooms * @VATParam + 
		      TurnOverDaymeetings * @VATParam + 
		      TurnOverRestaurant * @VATParam + 
		      TurnOverHalls * @VATParam + 
		      TurnOverOther * @VATParam), -3)/1000 AS TurnOverTotal, 
		    SUM(BonusPointsEarned), 
		    SUM(BonusPointsSpent)
		  FROM #OrderbookSum_Temp WITH (NOLOCK)
		  GROUP BY CustomerNumber
  END
  ELSE
  BEGIN
	  INSERT INTO #OrderbookSum 
		  SELECT 
		    DISTINCT CustomerNumber, 
		    SUM(TurnOverRooms * @VATParam) AS TurnOverRooms, 
		    SUM(NumberOfNightRooms) AS NumberOfNightRooms , 
		    SUM(NumberOfPaxRooms) AS NumberOfPaxRooms, 
		    SUM(NumberOfStaysRooms) AS NumberOfStaysRooms,
		    SUM(NumberOfCXLRooms) AS NumberOfCXLRooms, 
		    SUM(NumberOfNoShowRooms) AS NumberOfNoShowRooms, 
        SUM(TurnOverDaymeetings * @VATParam) AS TurnOverDaymeetings, 
		    SUM(NumberOfPaxDaymeetings) AS NumberOfPaxDaymeetings, 
	      SUM(NumberOfStaysDaymeetings) AS NumberOfStaysDaymeetings,
		    SUM(TurnOverHalls * @VATParam) AS TurnOverHalls, 
	      SUM(NumberOfHalls) AS NumberOfHalls, 
		    SUM(NumberOfPaxHalls) AS NumberOfPaxHalls, 
		    SUM(TurnOverRestaurant * @VATParam) AS TurnOverRestaurant, 
		    SUM(TurnOverOther * @VATParam) AS TurnOverOther, 
		    SUM(TurnOverRooms * @VATParam + 
		      TurnOverDaymeetings * @VATParam + 
		      TurnOverRestaurant * @VATParam + 
		      TurnOverHalls * @VATParam + 
		      TurnOverOther * @VATParam) AS TurnOverTotal, 
		    SUM(BonusPointsEarned), 
		    SUM(BonusPointsSpent)
		  FROM #OrderbookSum_Temp WITH (NOLOCK)
		  GROUP BY CustomerNumber
  END

	-- Add FilterSQL.
	SET @BaseFilterSQL = 'Select * from #OrderbookSum'

	if @TopList >= 0
		SET @BaseFilterSQL = 'Select TOP ' + cast(@TopList as varchar(10)) + ' * from #OrderbookSum'

	if @FilterSQL <> ''
	begin
		Set @FilterSQL = @BaseFilterSQL + ' where ' + @FilterSQL
	end
	else
	begin
		Set @FilterSQL = @BaseFilterSQL
	end
	-- Add OrderbySQL.
	if @OrderBySQL <> ''
	begin
		Set @FilterSQL = @FilterSQL + ' order by ' + @OrderBySQL
	end

	exec (@FilterSQL)

	DROP TABLE #OrderbookSum
	DROP TABLE #OrderbookSum_Temp
	DROP TABLE #CustomerLinks
	DROP TABLE #ReservationStatuses
	DROP TABLE #Segments
END