CREATE PROCEDURE DBO.UpdateReservationAddress @ACustomerNumber INT, @AAdressType INT, @AAddressNumber INT OUTPUT, @ARoomNumber INT = -1 AS

/*
  Created by Kent Johansen december 2006
  Used to update ReservationAddress - if @AAddressNumber = 0 then a new ReservationAddress is created.

  Changes:
    KJ 20131021: Discount per hotel
    VD20130902: Mail addresses now situated in a separate table - KUN i 8.2+!
    KJ 20100112: Ext: Added Owner calculation per room number

*/

DECLARE @Title VARCHAR(100)
DECLARE @FirstName VARCHAR(50)
DECLARE @LastName VARCHAR(50)
DECLARE @Address1 VARCHAR(50)
DECLARE @Address2 VARCHAR(50)
DECLARE @Address3 VARCHAR(50)
DECLARE @CountryCode VARCHAR(3)
DECLARE @ZipCode  VARCHAR(10)
DECLARE @City VARCHAR(50)
DECLARE @Phone  VARCHAR(30)
DECLARE @Fax VARCHAR(30)
DECLARE @Email VARCHAR(100)
DECLARE @AttentionName VARCHAR(50)
DECLARE @ProvisionType INT
DECLARE @ProvisionRate MONEY
DECLARE @RoomDiscountRate MONEY
DECLARE @MeetingDiscountRate MONEY
DECLARE @HallDiscountRate MONEY
DECLARE @DiscountRate MONEY
DECLARE @Link INT
DECLARE @PaymentAfterMonth INT
DECLARE @PaymentAfterDays INT
DECLARE @PaymentTypeWareNumber INT
DECLARE @CountryNumber INT
DECLARE @CompanyForm VARCHAR(15)

DECLARE @PaymentHotelSetting VARCHAR(10)

SELECT 
  @Title = Customer.Title,
  @FirstName = Customer.FirstName,
  @LastName = Customer.LastName,
  @Address1 = Customer.Address1,
  @Address2 = Customer.Address2,
  @Address3 = Customer.Address3,
  @Zipcode = Customer.ZipCodeOverwrite,
  @City = Customer.CityOverwrite,
  @Phone = Customer.PhoneNumber,
  @Fax = Customer.FaxNumber,
  @Email = MailAddresses.MailAddress,
  @ProvisionRate = Customer.ProvisionBill,
  @RoomDiscountRate = 0,
  @MeetingDiscountRate = 0,
  @HallDiscountRate = 0,
  @DiscountRate = 0,
  @PaymentAfterMonth = Customer.PaymentAfterMonth,
  @PaymentAfterDays = Customer.PaymentAfterDays,
  @PaymentTypeWareNumber = Customer.PaymentTypeWareNumber,
  @CountryNumber = Customer.CountryNumber,
  @CompanyForm = Customer.CompanyForm
FROM Customer WITH (NOLOCK) LEFT JOIN MailAddresses WITH (NOLOCK) ON 
  MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
WHERE 
  Customer.CustomerNumber = @ACustomerNumber

SELECT @CountryCode = CountryCode FROM Countries WHERE CountryNumber = @CountryNumber
SET @AttentionName = ''
SET @ProvisionType = 0
SET @Link = @ACustomerNumber

SELECT @PaymentHotelSetting = ISNULL(KeyValue, 0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'DAYSTOPAYAFTERCHECKOUT'

IF ISNULL(@PaymentAfterMonth, 0) = 0
  SELECT @PaymentAfterMonth = Number FROM DBO.iter_intlist_to_table_No_Dummy(@PaymentHotelSetting) WHERE ListPos = 1
IF ISNULL(@PaymentAfterMonth, 0) = 0
  SET @PaymentAfterMonth = 0

IF ISNULL(@PaymentAfterDays, 0) = 0
  SELECT @PaymentAfterDays = Number FROM DBO.iter_intlist_to_table_No_Dummy(@PaymentHotelSetting) WHERE ListPos = 2
IF ISNULL(@PaymentAfterDays, 0) = 0
  SET @PaymentAfterDays = 0

IF ISNULL(@AAddressNumber, 0) <= 0 OR NOT EXISTS (SELECT * FROM ReservationAddress WHERE AddressNumber = @AAddressNumber)
BEGIN
  --Create new address
  EXEC GetNextNumber 'ReservationAddressIdentity', 1, @AAddressNumber OUTPUT

  INSERT INTO ReservationAddress (AddressNumber, AddressType, Title, FirstName, LastName, Address1, Address2, Address3, CountryCode, ZipCode, City, Phone, Fax, Email, AttentionName, ProvisionType, ProvisionRate, RoomDiscountRate, MeetingDiscountRate, HallDiscountRate, DiscountRate, Link, PaymentAfterMonth, PaymentAfterDays, PaymentTypeWareNumber, CountryNumber, CompanyForm, FK_RoomNumber)
  VALUES (@AAddressNumber, @AAdressType, @Title, @FirstName, @LastName, @Address1, @Address2, @Address3, @CountryCode, @ZipCode, @City, @Phone, @Fax, @Email, @AttentionName, @ProvisionType, @ProvisionRate, @RoomDiscountRate, @MeetingDiscountRate, @HallDiscountRate, @DiscountRate, @Link, @PaymentAfterMonth, @PaymentAfterDays, @PaymentTypeWareNumber, @CountryNumber, @CompanyForm, @ARoomNumber)
END
ELSE
BEGIN
  --Update existing
  UPDATE ReservationAddress SET 
    AddressType=@AAdressType, Title=@Title, FirstName=@FirstName, LastName=@LastName, Address1=@Address1, Address2=@Address2,
    Address3=@Address3, CountryCode=@CountryCode, ZipCode=@ZipCode, City=@City, Phone=@Phone, Fax=@Fax, Email=@Email, AttentionName=@AttentionName, 
    ProvisionType=@ProvisionType, ProvisionRate=@ProvisionRate, RoomDiscountRate=@RoomDiscountRate, MeetingDiscountRate=@MeetingDiscountRate, 
    HallDiscountRate=@HallDiscountRate, DiscountRate=@DiscountRate, Link=@Link, PaymentAfterMonth=@PaymentAfterMonth, PaymentAfterDays=@PaymentAfterDays, 
    PaymentTypeWareNumber=@PaymentTypeWareNumber, CountryNumber=@CountryNumber, CompanyForm=@CompanyForm, FK_RoomNumber = @ARoomNumber
  WHERE AddressNumber = @AAddressNumber
END

