CREATE PROCEDURE DBO.RecalcAccountDaily @AStartDate DATETIME = 0 AS
SET NOCOUNT ON

-- KJ 20090810: Added departmentNumber to AccountDaily Req# 348
-- OF 20080701: Fixed for AStartDate null
-- KJ 20070312: Optimized.
-- KJ 20070312: Changed SMALLDATETIME to DATETIME
--NB20051107 Secured for DeleteOldFolios
--KJ20050712 Added AStartDateTime
--KS d. 18/05-2004

Declare @Debit bit
Declare @Price Money
Declare @AccountNumber int
Declare @RecalcDateStr char(8)
Declare @HotelCustomerNumber int
DECLARE @DeleteDateStored DATETIME, @tmpString CHAR(8)
DECLARE @FK_DepartmentNumber SMALLINT

SELECT @tmpString=IsNull(KeyValue,0) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey='FoliosDeletedBefore' -- SAME CODE IS FOUND IN GENERATEAVAIL SP
IF ISNULL(@tmpString, 0) = 0
  SET @DeleteDateStored = NULL
ELSE
  SET @DeleteDateStored = CONVERT(DATETIME, @tmpString, 112)

if isnull(@AStartDate,0) = 0
begin
  Delete From AccountDaily WHERE @DeleteDateStored IS NULL OR Date > CONVERT(varchar(8), @DeleteDateStored, 112)
  Declare #TransCur Cursor SCROLL Read_Only For
  SELECT SUM(PriceOverwrite), Debit, AccountNumber, Convert(char(8),DateStamp,112), HotelCustomerNumber, FK_DepartmentNumber
  FROM Transactions WITH (NOLOCK)
  WHERE @DeleteDateStored IS NULL OR CONVERT(DATETIME, DateStamp, 112) > @DeleteDateStored
  GROUP BY Debit, AccountNumber, Convert(char(8),DateStamp,112), HotelCustomerNumber, FK_DepartmentNumber
end
else
begin
  Delete From AccountDaily where Date > CONVERT(varchar(8), @AStartDate, 112) AND (@DeleteDateStored IS NULL OR Date >  CONVERT(varchar(8), @DeleteDateStored, 112))
  Declare #TransCur Cursor SCROLL Read_Only For
  SELECT SUM(PriceOverwrite), Debit, AccountNumber, Convert(char(8),DateStamp,112), HotelCustomerNumber, FK_DepartmentNumber
  FROM Transactions WITH (NOLOCK)  
  WHERE  (CONVERT(varchar(8), DateStamp, 112) > @AStartDate AND @DeleteDateStored IS NULL) OR (CONVERT(varchar(8), DateStamp, 112) > @AStartDate AND CONVERT(DATETIME, DateStamp, 112) > @DeleteDateStored)
  GROUP BY Debit, AccountNumber, Convert(char(8),DateStamp,112), HotelCustomerNumber, FK_DepartmentNumber
end

Open #TransCur

Fetch Next From #TransCur Into @Price,@Debit, @AccountNumber,@RecalcDateStr, @HotelCustomerNumber, @FK_DepartmentNumber

While (@@FETCH_STATUS = 0) -- safe
begin
  If @Debit = 1
  begin
    Update AccountDaily SET
      TurnOverdebit = TurnOverDebit + ABS(@Price)
    Where AccountNumber = @AccountNumber  AND Date = @RecalcDateStr AND HotelCustomerNumber= @HotelCustomerNumber AND FK_DepartmentNumber = @FK_DepartmentNumber

    if @@RowCount = 0 -- Nothing was updated -> insert row!
      INSERT INTO AccountDaily (AccountNumber, Date, TurnOverCredit,TurnOverDebit,AmountDebit, AmountCredit, HotelCustomerNumber, FK_DepartmentNumber) VALUES 
      (@AccountNumber, @RecalcDateStr, 0, ABS(@Price),0,0, @HotelCustomerNumber, @FK_DepartmentNumber)
  end
  else
  begin
    Update AccountDaily SET
      TurnOverCredit = TurnOverCredit + ABS(@Price)
    Where AccountNumber = @AccountNumber  AND Date = @RecalcDateStr AND HotelCustomerNumber= @HotelCustomerNumber AND FK_DepartmentNumber = @FK_DepartmentNumber


    if @@RowCount = 0 -- Nothing was updated -> insert row!
      INSERT INTO AccountDaily (AccountNumber, Date, TurnOverCredit,TurnOverDebit,AmountDebit, AmountCredit, HotelCustomerNumber, FK_DepartmentNumber) VALUES 
      (@AccountNumber, @RecalcDateStr, ABS(@Price),0,0,0, @HotelCustomerNumber, @FK_DepartmentNumber)
  end

  Fetch Next From #TransCur Into @Price,@Debit, @AccountNumber,@RecalcDateStr, @HotelCustomerNumber, @FK_DepartmentNumber
end

Close #TransCur
Deallocate #TransCur

