CREATE PROCEDURE dbo.UpdateMISTable @SystemDateStr CHAR(8), @Room_HallType TINYINT, @ReferenceNumber INT, @GuestNumber INT, @UpdateType TINYINT, @PackageSameDayAsLogi BIT, @BookedNumberOfRooms INT, @TotalAdults INT, @TotalChildren1 INT, @TotalChildren2 INT,	
  @TotalPriceDay MONEY,	@VatOfPrice MONEY, @VatOfPacket MONEY, @PackagePrice MONEY, @CurrentDateStr CHAR(8), @CurrentDateStrDeparturePackage CHAR(8), @SegmentNumber INT, @PointOfSaleNumber INT, @AGuestTypeNumber INT, @APurposeCodeNumber INT, @AllocatedTypeNumber INT, @ValueType TINYINT, @HotelCustomerNumber INT, 
  @Status SMALLINT, @CampaignCode SMALLINT, @ProjectCode SMALLINT, @StaffID INT, @WareGroupNumber INT, @ResultAccountNumber INT, @DepartmentNumber INT, @ProductWareLinkNumber BIGINT, @IsPackage BIT, @Text VARCHAR(256), @OwnerReservation BIT,@ProductNumber INT,
  @AWareTypeWare SMALLINT, @AWareNumber INT,@BookedWareCount INT, @ACountryNumber INT, @AFromTime DATETIME, @AToTime DATETIME AS

 SET NOCOUNT ON
/*
  Created by Kent Johansen July 2009
  Used to save in the MISTable

  Changes:
    KJ 20241009: Ext: Added From/To Time
    KJ 20240521: Bug: "Fix" for "Arithmetic overflow error converting expression to data type int
    KJ 20231218: Ext: Added Guesttype, purposeCode and country
    SG 20210224: Added BookedWareCount, removed @FolioNumber  
    SG 20210222: Avoid AccountNumber NULL
    SG 20200514: Added @AWareNumber
    SG 20200424: Added @AWareTypeWare in MISTable.WareType
    SG 20190710: Only store records with values <> 0. 
    OF 20120808: Ext: ProductNumber added to mis
    OF 20120502: Ext: OwnerReservation included.
    OF 20120425: Ext: MisTable is now used as detail view for MIS Detail viewer.
    OF 20120417: Ext: Primary keys removed and detailed mis introduced.
    OF 20120113: Ext: Text added for detailview.
    OF 20111208: Bug: Multiple changes to turnover arround systemdate. Rev. 2.
    OF 20111201: Bug: Pieces and pax arround systemdate was not correct. Som code reverted to old mis model.
    OF 20111125: Ext: Modifications to pax/pieces. Attempt 6. Pax and pieces are resolved from reservations only. I will soon give up..........
    OF 20110819: Ext: New mis version for handling budget to.
    OF 20110503: Ext: Staff, CampaignCode and ProjectCode added.
    OF 20110209: Bug: ReservationStatus added in as param in update mistable sections
    OF 20110208: Ext: Reservations Status added.
    OF 20091116: Ext: Storing in #MISTable_Source added. Fixed.

@UpdateType:
1: ?
2: ?
3: ?
4: ?
*/
  
IF @TotalPriceDay <> 0 or @PackagePrice <> 0 or @TotalAdults <> 0 or @TotalChildren1 <> 0 or @TotalChildren1 <> 0 or @BookedNumberOfRooms <> 0 
BEGIN
  IF @UpdateType = 1 OR @UpdateType = 3
  BEGIN
    SET @TotalAdults = 0
    SET @TotalChildren1 = 0
    SET @TotalChildren2 = 0
  END

  SET @ResultAccountNumber = IsNull(@ResultAccountNumber, 0)

  IF @UpdateType = 1 OR @UpdateType = 2
  BEGIN
    -- Update Actual
    IF @PackageSameDayAsLogi = 1
    BEGIN
      IF @IsPackage = 0
      BEGIN
        -- insert logi 
        INSERT INTO MISTable (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, FK_GuestTypeNumber, FK_PurposeCodeNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, ProductWareLinkNumber, IsPackage, Text, OwnerReservation, ProductNumber, 
          WareType, WareNumber, BookedWareCount, ActualWareCount, FK_CountryNumber, FromTime, ToTime)
         VALUES (@Room_HallType, @ReferenceNumber, @GuestNumber, -1, @CurrentDateStr, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, 0, 0, 0, 0, 0, 0, 0, 0, @TotalAdults, @TotalChildren1, @TotalChildren2, @BookedNumberOfRooms, @TotalPriceDay, @VatOfPrice, 0, 0, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, @IsPackage, @Text, @OwnerReservation, @ProductNumber, 
           @AWareTypeWare, @AWareNumber, 0, 0, @ACountryNumber, @AFromTime, @AToTime)
      END
      ELSE
      BEGIN
        -- Insert package
        INSERT INTO MISTable (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, FK_GuestTypeNumber, FK_PurposeCodeNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, ProductWareLinkNumber, IsPackage, Text, OwnerReservation, ProductNumber, 
          WareType, WareNumber, BookedWareCount, ActualWareCount, FK_CountryNumber, FromTime, ToTime)
        VALUES (@Room_HallType, @ReferenceNumber, @GuestNumber, -1, @CurrentDateStrDeparturePackage, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @PackagePrice, @VatOfPacket, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, @IsPackage, @Text, @OwnerReservation, @ProductNumber, 
          @AWareTypeWare, @AWareNumber, 0, 0, @ACountryNumber, @AFromTime, @AToTime)
      END
    END
    ELSE
    BEGIN
      -- Insert logi
      -- Insert package
      INSERT INTO MISTable (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, FK_GuestTypeNumber, FK_PurposeCodeNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, ProductWareLinkNumber, IsPackage, Text, OwnerReservation, ProductNumber, 
        WareType, WareNumber, BookedWareCount, ActualWareCount, FK_CountryNumber, FromTime, ToTime) 
      VALUES (@Room_HallType, @ReferenceNumber, @GuestNumber, -1, @CurrentDateStr, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, 0, 0, 0, 0, 0, 0, 0, 0, @TotalAdults, @TotalChildren1, @TotalChildren2, @BookedNumberOfRooms, @TotalPriceDay, @VatOfPrice, @PackagePrice, @VatOfPacket, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, @IsPackage, @Text, @OwnerReservation, @ProductNumber, 
        @AWareTypeWare, @AWareNumber, 0, 0, @ACountryNumber, @AFromTime, @AToTime)
    END
  END
  ELSE IF @UpdateType = 3 OR @UpdateType = 4
  BEGIN
    -- Insert booked
    IF @PackageSameDayAsLogi = 1
    BEGIN
      IF @IsPackage = 0
      BEGIN
        -- Insert logi
        -- booked
        INSERT INTO MISTable (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, FK_GuestTypeNumber, FK_PurposeCodeNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, ProductWareLinkNumber, IsPackage, Text, OwnerReservation, ProductNumber, 
          WareType, WareNumber, BookedWareCount, ActualWareCount, FK_CountryNumber, FromTime, ToTime)
        VALUES (@Room_HallType, @ReferenceNumber, @GuestNumber, -1, @CurrentDateStr, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, @TotalAdults, @TotalChildren1, @TotalChildren2, @BookedNumberOfRooms, @TotalPriceDay, @VatOfPrice, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, @IsPackage, @Text, @OwnerReservation, @ProductNumber, 
          @AWareTypeWare, @AWareNumber, @BookedWareCount, 0, @ACountryNumber, @AFromTime, @AToTime)
      END
      ELSE
      BEGIN
        -- booked
        -- Insert package
        INSERT INTO MISTable (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, FK_GuestTypeNumber, FK_PurposeCodeNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, ProductWareLinkNumber, IsPackage, Text, OwnerReservation, ProductNumber, 
          WareType, WareNumber, BookedWareCount, ActualWareCount, FK_CountryNumber, FromTime, ToTime) 
        VALUES (@Room_HallType, @ReferenceNumber, @GuestNumber, -1, @CurrentDateStrDeparturePackage, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, 0, 0, 0, 0, 0, 0, @PackagePrice, @VatOfPacket, 0, 0, 0, 0, 0, 0, 0, 0, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, @IsPackage, @Text, @OwnerReservation, @ProductNumber, 
          @AWareTypeWare, @AWareNumber, @BookedWareCount, 0, @ACountryNumber, @AFromTime, @AToTime)
      END
    END
    ELSE
    BEGIN
      -- Booked
      -- Insert logi
      -- Insert package
      INSERT INTO MISTable (Room_HallType, ReferenceNumber, GuestNumber, FolioNumber, DateStamp, SegmentNumber, PointOfSaleNumber, FK_GuestTypeNumber, FK_PurposeCodeNumber, RoomTypeNumber, BookedAdult, BookedChildren1, BookedChildren2, BookedNumberOfRooms, BookedPrice, BookedVatOfPrice, BookedPackage, BookedVatOfPackage, ActualAdult, ActualChildren1, ActualChildren2, ActualNumberOfRooms, ActualPrice, ActualVatOfPrice, ActualPackage, ActualVatOfPackage, ValueType, HotelCustomerNumber, ReservationStatus, CampaignCode, ProjectCode, StaffID, WareGroupNumber, AccountNumber, DepartmentNumber, ProductWareLinkNumber, IsPackage, Text, OwnerReservation, ProductNumber, 
        WareType, WareNumber, BookedWareCount, ActualWareCount, FK_CountryNumber, FromTime, ToTime) 
      VALUES (@Room_HallType, @ReferenceNumber, @GuestNumber, -1, @CurrentDateStr, @SegmentNumber, @PointOfSaleNumber, @AGuestTypeNumber, @APurposeCodeNumber, @AllocatedTypeNumber, @TotalAdults, @TotalChildren1, @TotalChildren2, @BookedNumberOfRooms, @TotalPriceDay, @VatOfPrice, @PackagePrice, @VatOfPacket, 0, 0, 0, 0, 0, 0, 0, 0, @ValueType, @HotelCustomerNumber, @Status, @CampaignCode, @ProjectCode, @StaffID, @WareGroupNumber, @ResultAccountNumber, @DepartmentNumber, @ProductWareLinkNumber, @IsPackage, @Text, @OwnerReservation, @ProductNumber, 
        @AWareTypeWare, @AWareNumber, @BookedWareCount, 0, @ACountryNumber, @AFromTime, @AToTime)
    END
  END 
END