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 Integer = 1, --0: Only show turnover on the primary item, 
                                 --1: Also show turnover consolidated in conserns, 
						   --2: Only show turnover consolidated in conserns and companies and agencies not member of a concern
  @Corrections BIT, 
  @IncludeExpiredBonusPoints BIT = 0, 
  @VATParam MONEY = 1,
  @AmountsInThousands BIT = 0

/*
  Changes:
    SG 20170118: Consolidate in concerns (links both ways)
    SG 20161219: Consolidate in concerns: Exclude contact persons and guests
    SG 20161214: IncludeDownLinks changed to Integer. = 2 added (se above) 
    SG 20161101: CR 113756: Segments and Hotels -> VarChar(MAX)
    SG 20160928: @IncludeDownLinks added again
    KJ: Req #2728: Added amount in thousands.
    VD 20111118 @IncludeDownLinks remioved as parameter. Downlinks included is always the case.
    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) 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) 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
		
		IF @IncludeDownLinks = 2 
		BEGIN
		  INSERT INTO #CustomerLinks --Companies and agencies not member of a concern
		    SELECT DISTINCT Number, Number 
		    FROM #MarketingSelectedCustomers
		    INNER JOIN Customer ON #MarketingSelectedCustomers.Number = Customer.CustomerNumber
		    WHERE NOT EXISTS (SELECT * FROM CustomerLinks 
		                      INNER JOIN Customer ON (Customer.CustomerNumber = CustomerLinks.CustomerLinkNumber OR Customer.CustomerNumber = CustomerLinks.CustomerNumber) AND
			  			                         Customer.MarkAsDeleted = 0 and Customer.CustomerTypeNumber IN (3, 4, 5, 7)-- Company Corp, Agency Corp, SalesChannel
						  WHERE LinkTypeNumber = 0 AND (CustomerLinks.CustomerNumber = #MarketingSelectedCustomers.Number OR CustomerLinks.CustomerLinkNumber = #MarketingSelectedCustomers.Number))
		   AND Customer.CustomerTypeNumber Not in (1,8)

          END
		ELSE
		BEGIN
	       INSERT INTO #CustomerLinks
		    SELECT DISTINCT Number, Number 
		    FROM #MarketingSelectedCustomers
		END
	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) 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) 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

       IF @IncludeDownLinks = 2
	  BEGIN
	    INSERT INTO #CustomerLinks --Companies and agencies not member of a concern
		 SELECT CustomerNumber, CustomerNumber 
		 FROM Customer
		 WHERE CustomerTypeNumber = @CustomerTypeNumber OR (@CustomerTypeNumber = 9 AND CustomerTypeNumber Not in (1,8))
		 AND NOT EXISTS (SELECT * FROM CustomerLinks 
		                 INNER JOIN Customer ON (Customer.CustomerNumber = CustomerLinks.CustomerLinkNumber OR Customer.CustomerNumber = CustomerLinks.CustomerNumber) AND
		                                        Customer.MarkAsDeleted = 0 and Customer.CustomerTypeNumber IN (3, 4, 5, 7)-- Company Corp, Agency Corp, SalesChannel
					  WHERE LinkTypeNumber = 0)
	  END 
	  ELSE
	  BEGIN
         INSERT INTO #CustomerLinks
	      SELECT CustomerNumber, CustomerNumber 
	      FROM Customer 
	      WHERE CustomerTypeNumber = @CustomerTypeNumber OR @CustomerTypeNumber = 9
       END
	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
