CREATE PROCEDURE dbo.MISGenerateAllotment AS

/*
  Created 2022-05 by SG
  Adds records to MIStable generated from Allotments
  Called from GenerateTurnoverMIS

  Changes:
  KJ 20241009: Ext: Added From/To Time
  KJ 20241008: Bug: Fix for "Wrong hotel number used when charge packet department enabled".
  KJ 20240528: Bug: Fix for "Charge discount as product is default = 1"
  KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
  KJ 20230412: Valuetype wrong for discount if ruuning charge discount as product
  2022-05-23: SG: Use NameListSetting from product instead of HotelSetup
  2022-05-19: KJ: Opt: don't make any calculations if number of rooms is 0
  2022-05-19: KJ: Bug: Fix for "Number of free allotment calculated wrong"
  2022-05-19, SG: Optimized number of records
  2022-05-16, SG: Optomized  a bit
  2022-05-12, SG: Case where there is no POS or Segment marked in product used in allotment

  Room_HallType = 2 --From Allotment
*/

DECLARE @SystemDateStr CHAR(8)
SELECT @SystemDateStr = Systemdate FROM SYSTEM
DECLARE @SystemDate DATETIME
SET @SystemDate = CONVERT(DATETIME, @SystemDateStr, 112)

--Fields for MISTable:
DECLARE @DateStamp DATETIME
DECLARE @SegmentNumber INT
DECLARE @PointOfSaleNumber INT
DECLARE @RoomTypeNumber INT
DECLARE @BookedAdult INT
DECLARE @BookedNumberOfRooms INT
DECLARE @HotelCustomerNumber INT
DECLARE @ProjectCode INT
DECLARE @ProductNumber INT
DECLARE @NameListSetting INT
DECLARE @StaffId INT           --HotelSetup
DECLARE @ReservationStatus INT --HotelSetup
DECLARE @BookedPackageType INT --HotelSetup, default 1=stay
DECLARE @LFromTime DATETIME
DECLARE @LToTime DATETIME
DECLARE @LArrivalTime DATETIME
DECLARE @LDepartureTime DATETIME

DECLARE @OrderBookBudgetCalcDiscount BIT
DECLARE @DiscountAsNettoPrice BIT
DECLARE @ChargeDiscountAsProduct BIT
DECLARE @ProductWareLinkNumber BIGINT

SELECT @OrderBookBudgetCalcDiscount = ISNULL(KeyValue, 0) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'OrderBookBudgetCalcDiscount'
SELECT @DiscountAsNettoPrice = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'DiscountAsNettoPrice'
SELECT @ChargeDiscountAsProduct = ISNULL(KeyValue, 1) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'ChargeDiscountAsProduct'
SELECT @ProductWareLinkNumber = MAX(ProductWareLinkNumber) + 1 FROM MISTable WITH (NOLOCK)

DECLARE @DiscountVatValueWare FLOAT
DECLARE @DiscountWareNumber INT --Not used
DECLARE @DiscountResultAccountNumber INT
DECLARE @DiscountMISValueType INT

SELECT @DiscountWareNumber = DiscountWareNumber,
  @DiscountResultAccountNumber = DiscountResultAccountNumber,
  @DiscountVatValueWare = DiscountVatValueWare,
  @DiscountMISValueType = DiscountMISValueType
FROM dbo.GetDiscountWareInfo()

DECLARE @DefaultMISGuestType INT
DECLARE @DefaultMISPurposeCode INT
DECLARE @LHotelCountryNumber INT

SELECT @DefaultMISGuestType = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'DefaultMISGuestType'
SET @DefaultMISGuestType = ISNULL(@DefaultMISGuestType, - 1)

SELECT @DefaultMISPurposeCode = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'DefaultMISPurposeCode'
SET @DefaultMISPurposeCode = ISNULL(@DefaultMISPurposeCode, - 1)

SELECT @LHotelCountryNumber = CONVERT(INT, KeyValue) FROM HotelSetUp WITH (NOLOCK) WHERE SearchKey = 'CountryCode'
IF @@ROWCOUNT = 0
  SELECT TOP 1 @LHotelCountryNumber = CountryNumber FROM Countries WITH (NOLOCK)

CREATE TABLE #PacketWareTable --Populated by CalcPacketPriceAccExt
(
  [ResultAccountNumber] [INT] NULL,
  [DepartmentNumber] [SMALLINT] NULL,
  [PacketWarePrice] [FLOAT] NULL,
  [VatOfPacketWarePrice] [FLOAT] NULL,
  [WareGroupNumber] [FLOAT] NULL,
  [WareName] [VARCHAR](50) NULL,
  [WareType] INT NULL,
  [WareNumber] INT NULL,
  [ProductWareLinkNumber] BIGINT NULL,
  [ProductNumber] INT NULL,
  [BookedWareCount] INT NULL,
  [HotelCustomerNumber] INT NULL,
  FromTime DATETIME NULL,
  ToTime DATETIME NULL
)

DECLARE @GetVatFrom INT
SELECT @GetVatFrom = ISNULL(dbo.GetHotelSetupKeyValue('BookkeepingReport', @HotelCustomerNumber), 0) --0=Segment, 1=POS, 2=Roomtype/HallType, 3=ProductType, 4=?, default=0

DECLARE @ThirdParam INT
SET @ThirdParam = CASE @GetVATFrom WHEN 0 THEN @SegmentNumber WHEN 1 THEN @PointOfSaleNumber WHEN 2 THEN @RoomTypeNumber WHEN 3 THEN @ProductNumber END

DECLARE @WareNumber INT
DECLARE @BalanceAccountNumber INT
DECLARE @ResultAccountNumber INT
DECLARE @VATAccountNumber INT
DECLARE @VatValue FLOAT
DECLARE @WareText VARCHAR(50)
DECLARE @WareGroupNumber INT = -1
DECLARE @WareType INT = 99

EXEC GetVatValueAndAccountNumbers 0, @GetVatFrom, @ThirdParam, @WareNumber OUTPUT, @BalanceAccountNumber OUTPUT, @ResultAccountNumber OUTPUT, @VATAccountNumber OUTPUT, @VatValue OUTPUT, @WareText OUTPUT, @WareGroupNumber OUTPUT, @WareType OUTPUT

--PRINT '@WareNumber='+CONVERT(VARCHAR, @WareNumber)+', @VatValue='+CONVERT(VARCHAR, @VatValue)+', @WareText='+@WareText

SELECT DISTINCT CASE WHEN ISNULL(RL.RoomNumber, 0) > 0 THEN Rooms.RoomTypeNumber ELSE rl.BookedTypeNumber END AS RoomType, NumberOfRooms, RL.ArrivalDate, RL.DepartureDate, RL.LineNumber, RM.AllotmentNumber
INTO #Sold
FROM dbo.ReservationRoomLine RL WITH (NOLOCK)
INNER JOIN dbo.ReservationRoomLineXTab RLX WITH (NOLOCK) ON RL.LineNumber = RLX.LineNumber 
  AND Rlx.ReferenceNumber > 0
  AND RL.DepartureDate > @SystemDate
INNER JOIN dbo.Reservations R WITH (NOLOCK) ON R.ReferenceNumber = RLX.ReferenceNumber 
  AND R.GuestNumber = RLX.GuestNumber 
  AND R.Status <= 6
INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber = R.ReferenceNumber 
LEFT OUTER JOIN dbo.Rooms WITH (NOLOCK) ON Rooms.RoomNumber = RL.RoomNumber
WHERE ISNULL(RM.AllotmentNumber, 0) > 0

--Delete all
DELETE FROM mistable WHERE Room_HallType = 2 --From Allotment

DECLARE AllotmentCur CURSOR FAST_FORWARD READ_ONLY FOR 
  SELECT DateStamp,
  ISNULL(sx.SegmentNumber, 0), --Just one, although more are possible
  ISNULL(px.PointOfSaleNumber, 0), --Just one, although more are possible
  al.RoomTypeNumber,
  r.DefaultPax AS BookedAdult,
  (
    SELECT al.InitialCount + al.TotalChange - 
      (
        SELECT ISNULL(SUM(NumberOfRooms), 0) FROM #Sold s
        WHERE s.DepartureDate > al.DateStamp AND s.ArrivalDate <= al.datestamp
        AND s.RoomType = al.RoomTypeNumber
        AND s.AllotmentNumber = al.AllotmentNumber
      )
  ),
  c.CustomerNumber AS HotelCustomerNumber,
  ISNULL(a.FK_ProjectCode, -1) AS ProjectCode,
  p.ProductNumber,
  p.NameListSetting
  FROM AllotmentLines al WITH (NOLOCK)
  INNER JOIN Allotments a WITH (NOLOCK) ON a.AllotmentNumber = al.AllotmentNumber
    AND a.Active <> 0
    AND ISNULL(a.MarkAsDeleted, 0) = 0
  INNER JOIN AllotmentHotelXTAB x WITH (NOLOCK) ON x.AllotmentNumber = a.AllotmentNumber
  INNER JOIN Products p WITH (NOLOCK) ON a.FK_ProductNumberMIS = p.ProductNumber
    AND p.MarkAsDeleted = 0
  INNER JOIN RoomTypes r WITH (NOLOCK) ON r.RoomTypeNumber = al.RoomTypeNumber
  INNER JOIN Customer c WITH (NOLOCK) ON c.HotelNumber = r.HotelNumber
    AND CustomerTypeNumber = 7
  LEFT OUTER JOIN ProductSegmentsXTab sx WITH (NOLOCK) ON sx.ProductNumber = p.ProductNumber 
  LEFT OUTER JOIN ProductPOSXtab px WITH (NOLOCK) ON px.ProductNumber = p.ProductNumber
  WHERE al.DateStamp > @SystemDate
  AND x.HotelCustomerNumber = c.CustomerNumber
	AND (sx.SegmentNumber = (SELECT MIN(SegmentNumber) FROM ProductSegmentsXTab sx2 WITH (NOLOCK) WHERE sx2.ProductNumber = p.ProductNumber)
		OR ISNULL((SELECT MIN(SegmentNumber) FROM ProductSegmentsXTab sx2 WITH (NOLOCK) WHERE sx2.ProductNumber = p.ProductNumber), 0) = 0)
  AND (px.PointOfSaleNumber = (SELECT MIN(PointOfSaleNumber) FROM ProductPOSXtab px2 WITH (NOLOCK) WHERE px2.ProductNumber = p.ProductNumber)     
    OR ISNULL((SELECT MIN(PointOfSaleNumber) FROM ProductPOSXtab px2 WITH (NOLOCK) WHERE px2.ProductNumber = p.ProductNumber), 0) = 0)
  AND (
    SELECT al.InitialCount + al.TotalChange - 
      (
        SELECT ISNULL(SUM(NumberOfRooms), 0) 
        FROM #Sold s
        WHERE s.DepartureDate > al.DateStamp AND s.ArrivalDate <= al.DateStamp
        AND s.RoomType = al.RoomTypeNumber
        AND s.AllotmentNumber = al.AllotmentNumber
      )
  ) > 0
OPEN AllotmentCur;

FETCH NEXT FROM AllotmentCur INTO @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, @BookedAdult, @BookedNumberOfRooms, @HotelCustomerNumber, @ProjectCode, @ProductNumber, @NameListSetting

WHILE(@@FETCH_STATUS <> -1)
BEGIN
  DELETE FROM #PacketWareTable; -- Clear #PacketWareTable
  --Output:
  DECLARE @Addon MONEY
  DECLARE @BillTextProduct VARCHAR(50)
  DECLARE @BillTextAddOn VARCHAR(50)
  DECLARE @CalcPriceDay MONEY
  DECLARE @VatOfPriceDay MONEY
  DECLARE @PacketNumber INT
  DECLARE @EnvironmentFee SMALLMONEY
  DECLARE @FixedPrice BIT
  DECLARE @VATProduct BIT
  DECLARE @SpecifiedDepartureAddOn BIT
  DECLARE @EnvironmentFeeType FLOAT = 0
  DECLARE @DepartmentNumber SMALLINT
  DECLARE @OnePersonPaysForAll BIT

  SELECT @OnePersonPaysForAll = dbo.GetHotelSetupKeyValue('OnePersonPaysForAll', @HotelCustomerNumber)
  SELECT @StaffId = ISNULL(dbo.GetHotelSetupKeyValue('MISAllotmentStaffId', @HotelCustomerNumber), 0)
  SELECT @ReservationStatus = ISNULL(dbo.GetHotelSetupKeyValue('MISAllotmentReservationStatus', @HotelCustomerNumber), 0)
  SELECT @BookedPackageType = ISNULL(dbo.GetHotelSetupKeyValue('MISAllotmentPackageType', @HotelCustomerNumber), 1) --default: 1 (0: Arrival, 1: Stay, 2: Departure)

  SELECT TOP 1 @DepartmentNumber = d.PK_Department
  FROM  Departments d WITH (nolock)
  WHERE d.HotelNumber = ISNULL((SELECT HotelNumber FROM RoomTypes WITH (NOLOCK) WHERE  RoomTypeNumber = @RoomTypeNumber), 0)
  AND ISNULL(d.MarkAsDeleted, 0) = 0
  AND d.AutoCharge = 1
  
  SET @DepartmentNumber = ISNULL(@DepartmentNumber, -1);

  -- In order to read the right package, we create artificially arrival- and departuredates
  DECLARE @ArrDate DATETIME = @DateStamp
  DECLARE @DepDate DATETIME = @DateStamp

  IF @BookedPackageType = 0 --arrival
    SET @DepDate = DATEADD(day, 1, @Datestamp)
  IF @BookedPackageType = 1 --stay
  BEGIN
    SET @ArrDate = DATEADD(day, -1, @Datestamp)
    SET @DepDate = DATEADD(day, 1, @Datestamp)
  END

  IF @BookedPackageType = 2 --departure
    SET @ArrDate = DATEADD(day, -1, @Datestamp)

  EXEC ProductPricesDay @ProductNumber, @ArrDate, @DepDate, @DateStamp, @RoomTypeNumber, @BookedNumberOfRooms, @BookedAdult, 0, 0, @Addon OUTPUT, @BillTextProduct OUTPUT, @BillTextAddOn OUTPUT, @CalcPriceDay OUTPUT, @PacketNumber OUTPUT, @EnvironmentFee OUTPUT, @FixedPrice OUTPUT, @VATProduct OUTPUT, @SpecifiedDepartureAddOn OUTPUT, @NameListSetting
       
  SET @VatOfPriceDay = @CalcPriceDay * (1 - (1 / CONVERT(FLOAT, ((@VatValue / 100) + 1))))

  DECLARE @PackagePriceProduct MONEY= 0
  DECLARE @PackagePriceProductVat MONEY= 0
       
  IF @PacketNumber > 0
    EXEC CalcPacketPriceAccExt 0, @RoomTypeNumber, @DateStamp, @PacketNumber, @ProductNumber, @BookedAdult, 0, 0, @BookedNumberOfRooms, @HotelCustomerNumber, @PackagePriceProduct OUTPUT, @PackagePriceProductVat OUTPUT, @NameListSetting, @OnePersonPaysForAll, 0, 0, 1, -1, -1, 0, 0, @ProductWareLinkNumber, 1

  --PRINT CONVERT(VARCHAR, @DateStamp)+', @SegmentNumber='+CONVERT(VARCHAR, @SegmentNumber)+', @PointOfSaleNumber='+CONVERT(VARCHAR, @PointOfSaleNumber)+', @RoomTypeNumber='+CONVERT(VARCHAR, @RoomTypeNumber)+', @BookedAdult='+CONVERT(VARCHAR, @BookedAdult)+', @BookedNumberOfRooms='+CONVERT(VARCHAR, @BookedNumberOfRooms)+', @HotelCustomerNumber='+CONVERT(VARCHAR, @HotelCustomerNumber)+', @ProductNumber='+CONVERT(VARCHAR, @ProductNumber)+', @CalcPriceDay='+CONVERT(VARCHAR, @CalcPriceDay)+', @FixedPrice='+CONVERT(VARCHAR, @FixedPrice)+',@VATProduct='+CONVERT(VARCHAR, @VATProduct)+', @PacketNumber='+CONVERT(VARCHAR, @PacketNumber)+', @PackagePriceProduct='+CONVERT(VARCHAR, @PackagePriceProduct)

  DECLARE @DateStampStr CHAR(8) = CONVERT(CHAR(8), @DateStamp, 112)
  SET @BookedAdult = @BookedAdult * @BookedNumberOfRooms
  
  -- Time can not be determined - set to whole day
  SET @LArrivalTime = CAST(0 AS DATETIME)
  SET @LDepartureTime = CAST('23:59:59.997' AS DATETIME)

  EXEC MisStoreLogiAndCalcDiscount 2, @OrderBookBudgetCalcDiscount, @DiscountAsNettoPrice, @ChargeDiscountAsProduct, @DiscountVatValueWare, 0, 0, 4, 0, 1, @BookedNumberOfRooms, @BookedAdult, 0, 0, -1, -1, @SystemDateStr, @DateStampStr, @DateStampStr, 
    @SegmentNumber, @PointOfSaleNumber, @DefaultMISGuestType, @DefaultMISPurposeCode, @RoomTypeNumber, @HotelCustomerNumber, @ReservationStatus, 0, 0, @StaffID, @WareGroupNumber, @WareNumber, 0, @DepartmentNumber, @ProductWareLinkNumber, 
    @ResultAccountNumber, @DiscountResultAccountNumber, @CalcPriceDay, @VatOfPriceDay, @PackagePriceProduct, @PackagePriceProductVat, @ProductNumber, @DiscountMISValueType, @DiscountWareNumber, @LHotelCountryNumber, @LArrivalTime, @LDepartureTime

  -- Store Packages
  DECLARE @ResultAccountPackage INT
  DECLARE @DepartmentNumberPackage SMALLINT
  DECLARE @PacketWarePrice FLOAT
  DECLARE @VatOfPacketWarePrice FLOAT
  DECLARE @WareName VARCHAR(50)
  DECLARE @ProductWareLinkNumberMis BIGINT
  DECLARE @ProductNumberPacket INT
  DECLARE @BookedWareCountPacket INT

  DECLARE PacketWareCur CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT * FROM #PacketWareTable WITH (NOLOCK)
  OPEN PacketWareCur

  FETCH NEXT FROM PacketWareCur INTO @ResultAccountPackage, @DepartmentNumberPackage, @PacketWarePrice, @VatOfPacketWarePrice, @WareGroupNumber, @WareName, @WareType, @WareNumber, @ProductWareLinkNumberMis, @ProductNumberPacket, @BookedWareCountPacket, @HotelCustomerNumber, @LFromTime, @LToTime
  
  DECLARE @PackageStored BIT

  SET @PackageStored = 0
  IF(@@FETCH_STATUS <> -1)
    SET @PackageStored = 1
  
  WHILE(@@FETCH_STATUS <> -1)
  BEGIN
    IF @DateStamp = @SystemDate
    BEGIN
      IF @SystemDate < @DepDate  --@ProductEndDate
        SET @ProductWareLinkNumberMis = -200 -- Later in MisUpdateProductWareLinkNumbers this packet will be pared with product that is already posted in actual
      ELSE
        SET @ProductWareLinkNumberMis = -100
    END
    
    SET @DepartmentNumberPackage = ISNULL(@DepartmentNumberPackage, -1)
    SET @ResultAccountPackage = ISNULL(@ResultAccountPackage, -1)

    IF @PacketWarePrice <> 0 OR @VatOfPacketWarePrice <> 0 OR @BookedWareCountPacket <> 0
    BEGIN
      IF @ProductWareLinkNumberMis <> -100 -- SG/20130124: See above
      BEGIN
        EXEC UpdateMISTable @SystemDateStr, 2, -1, -1, 4, 0, 0, 0, 0, 0, 0, 0, @VatOfPacketWarePrice, @PacketWarePrice, @DateStampStr, @DateStampStr, 
          @SegmentNumber, @PointOfSaleNumber, @DefaultMISGuestType, @DefaultMISPurposeCode, @RoomTypeNumber, 1, @HotelCustomerNumber, @ReservationStatus, 0, 0, @StaffID, @WareGroupNumber, @ResultAccountPackage, @DepartmentNumberPackage, @ProductWareLinkNumberMis, 1, @WareName, 0, @ProductNumberPacket, @WareType,
          @WareNumber, @BookedWareCountPacket, @LHotelCountryNumber, @LFromTime, @LToTime
      END
    END

    FETCH NEXT FROM PacketWareCur INTO @ResultAccountPackage, @DepartmentNumberPackage, @PacketWarePrice, @VatOfPacketWarePrice, @WareGroupNumber, @WareName, @WareType, @WareNumber, @ProductWareLinkNumberMis, @ProductNumberPacket, @BookedWareCountPacket, @HotelCustomerNumber, @LFromTime, @LToTime
  END

  CLOSE PacketWareCur
  DEALLOCATE PacketWareCur

  FETCH NEXT FROM AllotmentCur INTO @DateStamp, @SegmentNumber, @PointOfSaleNumber, @RoomTypeNumber, @BookedAdult, @BookedNumberOfRooms, @HotelCustomerNumber, @ProjectCode, @ProductNumber, @NameListSetting
  
  SET @ProductWareLinkNumber = @ProductWareLinkNumber + 1;
END --while (AllotmentCur)
CLOSE AllotmentCur
DEALLOCATE AllotmentCur

DROP TABLE #Sold