CREATE PROCEDURE [dbo].[GA_AllotmentRelease] @SystemDateStr CHAR (8), @NightAuditRunning BIT AS
SET NOCOUNT ON
/*
    Created by Jacob Pedersen

    Last Changed:
    JP20060626 OPTIMIZE: prefixed tables with dbo.
    JP20060503 BUG: clean up roombookingstatus and release one day more in the past
    AV+JP20060131: AvailBeforeRelease is only set if it is null
    JP20051206: Release one day before also!
    JP20051020: BUG: allotment is released for one day too many in nightaudit
    JP20050926: OPTIMIZE: 	Removed: TotalChange-TotalChange = 0. No change in time spent though
				Added: MarkAsDeleted IS NULL AND
    JP20050919 OPTIMIZE: removed "AND TotalChange <> ..." part to make SQL faster (now takes less than 4 minutes at Skallerup - still slow)
    JP20050912 EXT: added cleanup of allotmentlines to nightaudit
    JP20050614 EXT: use dbo.GetCurrentCount and do not release in the past, because then it takes 11 minutes at some customers

  NOTE:
	i+t-c=0
	t=c-i
*/

UPDATE dbo.AllotmentLines SET
  TotalChange=dbo.GetCurrentCount(AllotmentLines.AllotmentNumber,RoomTypeNumber,DateStamp)-InitialCount,
  AvailBeforeRelease = (SELECT CASE AvailBeforeRelease WHEN NULL THEN (InitialCount+TotalChange-dbo.GetCurrentCount(AllotmentLines.AllotmentNumber,RoomTypeNumber,DateStamp)) ELSE AvailBeforeRelease END FROM dbo.AllotmentLines A WHERE A.AllotmentNumber = AllotmentLines.AllotmentNumber AND  A.DateStamp = AllotmentLines.DateStamp AND  A.RoomTypeNumber = AllotmentLines.RoomTypeNumber)
FROM dbo.Allotments, dbo.AllotmentLines
--WHERE Allotments.AllotmentNumber=AllotmentLines.AllotmentNumber AND MarkAsDeleted IS NULL AND CONVERT(DATETIME, DateStamp, 112) BETWEEN CONVERT(DATETIME, @SystemDateStr, 112) - 2 AND CONVERT(DateTime,@SystemDateStr, 112) - 1 + IsNull(ReleaseDays, 0)
WHERE Allotments.AllotmentNumber=AllotmentLines.AllotmentNumber AND CONVERT(DATETIME, DateStamp, 112) BETWEEN CONVERT(DATETIME, @SystemDateStr, 112) - 2 AND CONVERT(DateTime,@SystemDateStr, 112) - 1 + IsNull(ReleaseDays, 0)

-- Clean up blank rows which was created when checking for overbooking
IF @NightAuditRunning = 1
BEGIN
  DELETE FROM dbo.AllotmentLines WHERE InitialCount=0 AND TotalChange=0 AND (AvailBeforeRelease=0 OR AvailBeforeRelease IS NULL) AND dbo.GetCurrentCount(AllotmentNumber,RoomTypeNumber,DateStamp)=0 AND DateStamp<@SystemDateStr
  UPDATE dbo.RoomBookingStatus SET FreeRoomsAllotment=0
  DELETE FROM dbo.RoomBookingStatus WHERE DateStamp>@SystemDateStr
END


