CREATE PROCEDURE [dbo].[OnlineUpdateHallLineWithCampaign]
  @AReferenceNumber INT
  , @ACampaignText VARCHAR(50)
  , @AHotelChainNumber INT = 0
 AS

SET NOCOUNT ON
/*
 LJE20120704: created.
*/
BEGIN

  declare @CustomerNumber int
  declare @CampaignCode int
  declare @DiscountCustomerType int -- 1:Guest, 2:Company, 3:Agency, 4:Agent.
  declare @DiscountType int -- 0:All, 1:Rooms, 2:Meeting, 3:Halls, 4:Phone, 5:PayTV, 6:Owner.
  declare @DiscountPct float
  declare @AddressNumber int
  
  -- Find HotelCustomerNumber.
  select @CustomerNumber = CustomerNumber 
  from Customer 
  where HotelNumber = @AHotelChainNumber
  
  -- Find CampaignCode.
  select @CampaignCode = cc.PK_CampaignCode 
  from CampaignCodes cc
    INNER JOIN CampaignCodesHotel cch ON cc.PK_CampaignCode = cch.PK_CampaignCode
  where cc.[Text] = @ACampaignText
    and cch.FK_HotelCustomerNumber = @CustomerNumber
  
  -- Find DiscountType, and DiscountPct/Value.
  select @DiscountType = DiscountType
    , @DiscountPct = Value 
  from CampaignCodesDiscount where PK_CampaignCode = @CampaignCode
  
  -- Find DiscountCustomerType.
  select @DiscountCustomerType = DiscountCustomerType 
  from CampaignCodesHotel 
  where PK_CampaignCode = @CampaignCode

  -- Find AddressNumber - link from Reservations to ReservationAddress.
  select @AddressNumber = AddressNumber 
  from ReservationAddressXTab
  where ReferenceNumber = @AReferenceNumber
  
  -- Update Reservations table.
  update Reservations 
  set FK_CampaignCode = @CampaignCode
    ,DiscountCustomerType = @DiscountCustomerType 
  where ReferenceNumber = @AReferenceNumber

  -- Update ReservationAddress table.  
  if (@DiscountType = 0)  -- Discount: All.
  BEGIN
    update ReservationAddress 
    set DiscountRate = @DiscountPct, AddressType = @DiscountCustomerType
    where AddressNumber = @AddressNumber
  END
  
  if (@DiscountType = 2)  -- Discount: Meeting, price per participant.
  BEGIN  
    update ReservationAddress 
    set MeetingDiscountRate = @DiscountPct, AddressType = @DiscountCustomerType 
    where AddressNumber = @AddressNumber
  END  

END

