CREATE PROCEDURE DBO.AutoPaymentFolioSaldoToZero @ReferenceNumber int, @GuestNumber smallint, @AddressTypeNumber smallint AS
SET NOCOUNT ON

/*

Changes:
 KJ 20070410: Department rewrite.
 KJ 20060504: AutoCharge department read from departments
 KJ 20060124: Fix for "Formal parameter @HotelCustomernumber was defined as output but the actual parameter not declared output" - error in AutoExtendStay plugin in nightaudit
 20050526 KJ changed to use GetNextNumber
 20020227 KS
*/
Declare @Saldo money

Exec GetSaldoOnFolio @ReferenceNumber, @GuestNumber, @AddressTypeNumber, @Saldo OUTPUT

if @Saldo <> 0
begin
  Declare @I smallint
  Declare @NumberOfRepeats SmallInt
  Declare @FolioNumber integer
  Declare @PaymentTypeNumber smallint
  Declare @PaymentWareNumber int
  DECLARE @FK_PaymentOnDepartment INT
  Declare @PaymentDatetime DateTime
  Declare @UserIDNumber int
  Declare @TransNrShowOnBillOnAdd int
  Declare @CurrentDateStr Char(8)
  Declare @BillNumber int
  Declare @DummySaldo money
  DECLARE @HotelCustomerNumber INT
  DECLARE @HotelNumber INT
  DECLARE @PkInvoice INT

  SELECT @HotelNumber = HotelNumber FROM ReservationMain WITH (NOLOCK) WHERE ReferenceNumber = @ReferenceNumber

  SELECT @HotelCustomerNumber =  CustomerNumber
  FROM Customer WITH (NOLOCK)
  WHERE HotelNumber = @HotelNumber
  AND HotelNumber>=0
  AND CustomerTypeNumber=7

  IF ISNULL(@HotelCustomerNumber,-1) = -1 SET @HotelCustomerNumber = -1
  IF ISNULL(@HotelNumber,-1) = -1 SET @HotelNumber = -1

  Select @UserIDNumber = UserIDNumber from Personel WITH (NOLOCK) WHERE UserTypeNumber = 4

  SELECT @CurrentDateStr = SYSTEMDATE FROM System WITH (NOLOCK)  WHERE NUMBER = 1  -- Only one record

  SELECT TOP 1 @FK_PaymentOnDepartment = PK_Department FROM Departments WITH (NOLOCK) WHERE AutoCharge = 1 AND IsNull(HotelNumber, -1) = @HotelNumber ORDER BY DepartmentNumber

  SET @PaymentTypeNumber = Convert(SmallInt,(Select KeyValue from HotelSetup WITH (Nolock) where Searchkey = 'PaymentTerm'))
  SET @PaymentWareNumber = Convert(Int,(Select KeyValue from HotelSetup WITH (Nolock) where Searchkey = 'PaymentTermWareNumber'))

  if  @AddressTypeNumber = -1
    SET @NumberOfRepeats = 4
  else
    SET @NumberOfRepeats = 1

  SET @I = 1

  While (@I<=@NumberOfRepeats)
  begin
    if @NumberOfRepeats = 4
    begin
      SET @AddressTypeNumber = @I
      exec GetSaldoOnFolio @ReferenceNumber, @GuestNumber, @AddressTypeNumber, @Saldo OUTPUT
    end
    if @Saldo <> 0
    begin
      SET @FolioNumber = -1

      SELECT @FolioNumber = FolioNumber
      FROM FOLIOS WITH (NOLOCK) INNER JOIN RESERVATIONADDRESS WITH (NOLOCK) ON FOLIOS.ReservationAddressNumber = RESERVATIONADDRESS.AddressNumber  INNER JOIN
        RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER
      WHERE ReservationAddressXTab.ReferenceNumber=@ReferenceNumber and ReservationAddressXTab.GuestNumber=@GuestNumber and  ReservationAddress.AddressType = @AddressTypeNumber

      if @FolioNumber >-1
      begin
        SET @PaymentDatetime = Convert(DateTime,@CurrentDateStr+' '+Convert(Char(8),CURRENT_TIMESTAMP,108))
        exec Postering @PaymentDatetime, 0, 0, 0, 0, 0, @UserIDNumber, @FolioNumber, 0, 0, 0, 0, '', 1, @Saldo, 1, 0, 0, 0, 0, @FK_PaymentOnDepartment, @PaymentWareNumber, @PaymentTypeNumber, 0, 0, 0, '', @TransNrShowOnBillOnAdd OUTPUT
        Exec GetSaldoOnFolio @ReferenceNumber, @GuestNumber, @AddressTypeNumber, @Saldo OUTPUT
        if @Saldo = 0 
        begin
          update Folios Set OpenFolio = 0 where FolioNumber=@FolioNumber
          EXEC GetNextNumber 'BILLIDENTITY', 1, @BillNumber OUTPUT, @HotelCustomerNumber
          INSERT INTO Invoice (FkCustomerHotel, FkDepartment, FkStaff, BillNumber, CreatedDate)
          VALUES (@HotelCustomerNumber, @FK_PaymentOnDepartment, @UserIDNumber, @BillNumber, @PaymentDatetime)
          SET @PkInvoice = @@IDENTITY
          INSERT INTO InvoiceTransactionXTab (FkInvoice, FkTransaction)
            SELECT @PkInvoice, TransactionNumber FROM Transactions WITH (NOLOCK)
            WHERE FolioNumber = @FolioNumber and ShowOnBillFlag = 1 and MovedDeleted = 0
            EXCEPT
            SELECT @PkInvoice, TR.TransactionNumber FROM Transactions TR WITH (NOLOCK)
              INNER JOIN InvoiceTransactionXTab ITX WITH (NOLOCK) ON TR.TransactionNumber = ITX.FkTransaction
              INNER JOIN Invoice I WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice AND I.CancelDate IS NULL
            WHERE TR.FolioNumber = @FolioNumber;
        end
      end
    end
    SET @I = @I+1
  end
end