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:
    vd 20191001: Provisions per hotel. Same solution as in KJ 20131021
    vd 20181018: EANs now located in a new table.
    KJ 20131021: Discount per hotel
    VD20130902: Mail addresses now situated in a separate table
    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,
  @ProvisionType = 0,
  @ProvisionRate = 0,
  @RoomDiscountRate = 0,
  @MeetingDiscountRate = 0,
  @HallDiscountRate = 0,
  @DiscountRate = 0,
  @PaymentAfterMonth = Customer.PaymentAfterMonth,
  @PaymentAfterDays = Customer.PaymentAfterDays,
  @PaymentTypeWareNumber = Customer.PaymentTypeWareNumber,
  @CountryNumber = Customer.CountryNumber,
  @CompanyForm = CustomerEANS.EAN
FROM Customer WITH (NOLOCK) 
  LEFT JOIN MailAddresses WITH (NOLOCK) ON MailAddresses.CustomerNumber = Customer.CustomerNumber AND MailAddresses.MailAddressType = 0
  LEFT JOIN CustomerEANs WITH (NOLOCK) ON CustomerEANs.CustomerNumber = Customer.CustomerNumber AND CustomerEANs.EANType = 0
WHERE 
  Customer.CustomerNumber = @ACustomerNumber

SELECT @CountryCode = CountryCode FROM Countries WHERE CountryNumber = @CountryNumber
SET @AttentionName = ''
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

