CREATE PROCEDURE [dbo].[OfficeHotelManager] @AYear INT, @AHotelNumberStr VARCHAR(100), @InclVAT BIT, @InclPackage BIT, @AGroupBySegment BIT, @ASegmentCSV VARCHAR(500), @AIncludeProvision BIT AS
BEGIN

/*
  Changes:
    KJ 20200428: Ext: Added provision in MIS. Req #3788
    KJ 20190118: Ext: Segment added
    KJ 20120712: Ext: MISTableView rewrite
    KJ 20120710: Ext: MISTableView rewrite
    KJ 20120216: Ext: Use MISTableView to select data.  
*/

SET NOCOUNT ON

CREATE TABLE #OfficeManager 
(
  MonthNumber INT,
  SegmentNumber INT,
  Price MONEY
)

DECLARE @SystemDate CHAR(8)
SELECT @SystemDate = CONVERT(CHAR(8), CONVERT(DATETIME, systemdate, 112), 112) FROM system WITH (NOLOCK)

DECLARE @ValueType VARCHAR(10)
SET @ValueType = '2,3' -- Daymeeting + Halls

DECLARE @MonthNumber INT 
DECLARE @SegmentNumber INT
DECLARE @Price MONEY
DECLARE @PriceExPackage MONEY
DECLARE @PriceExPackageExVat MONEY
DECLARE @PriceInclPackageExVat MONEY

DECLARE db_cur CURSOR FOR
  SELECT
    MONTH(CONVERT(DateTime, DateStamp, 112)),
    MISTableView.SegmentNumber,
    sum(ActualPriceInclPackage)+sum(BookedPriceInclPackage), 
    sum(ActualPriceExPackage)+sum(BookedPriceExPackage), 
    sum(ActualPriceExPackageExVat)+sum(BookedPriceExPackageExVat), 
    sum(ActualPriceInclPackageExVat)+sum(BookedPriceInclPackageExVat)
  FROM MISTableView WITH (NOLOCK) 
  where 1=1--MisData in (1)
  AND DateStamp LIKE CONVERT(VARCHAR, @AYear) + '%'
  AND ValueType IN (SELECT Value FROM DBO.SplitNumberList(@ValueType))
  AND ((@AHotelNumberStr = '') OR (HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@AHotelNumberStr)))) 
  AND (@ASegmentCSV = '' OR MISTableView.SegmentNumber IN (SELECT Value FROM DBO.SplitNumberList(@ASegmentCSV)))
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  GROUP BY MONTH(CONVERT(DateTime, DateStamp, 112)), MISTableView.SegmentNumber
OPEN db_cur

FETCH NEXT FROM db_cur INTO @MonthNumber, @SegmentNumber, @Price, @PriceExPackage, @PriceExPackageExVat, @PriceInclPackageExVat

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @InclVAT = 1 AND @InclPackage = 0
    SET @Price = @PriceExPackage
  ELSE IF @InclVAT = 0 AND @InclPackage = 0
    SET @Price = @PriceExPackageExVat
  ELSE IF @InclVAT = 0 AND @InclPackage = 1
    SET @Price = @PriceInclPackageExVat

  IF @AGroupBySegment = 1
    INSERT INTO #OfficeManager VALUES (@MonthNumber, @SegmentNumber, @Price) 
  ELSE
  BEGIN
    UPDATE #OfficeManager SET Price = Price + @Price WHERE MonthNumber = @MonthNumber
    IF @@ROWCOUNT = 0 
      INSERT INTO #OfficeManager VALUES (@MonthNumber, -1, @Price) 
  END
  FETCH NEXT FROM db_cur INTO @MonthNumber, @SegmentNumber, @Price, @PriceExPackage, @PriceExPackageExVat, @PriceInclPackageExVat
END

SELECT * FROM #OfficeManager ORDER BY MonthNumber

CLOSE db_cur
DEALLOCATE db_cur

DROP TABLE #OfficeManager

END