CREATE FUNCTION dbo.NeedDayProductDepartureCharge  (@ReferenceNumber INT, @GuestNumber INT, @CurrentDate DATETIME)
RETURNS INT
/*
  KJ20090812 - Bug: Handle product can be posted on table folio
  NB20060406 - Only on lines with product or package or Price > 0
  NB20060328 - Not charged Day Products at departure day
*/
AS
BEGIN
  DECLARE @Return INT
  DECLARE @cnt INT

  --nb20060328 - 3 - Not charged Day Products at departure day (halls and tables)
  IF @GuestNumber = 0  
  BEGIN
    DECLARE @SystemDate DATETIME
    , @MainResDepartureDate DATETIME
    , @LastHallDepartureDate DATETIME
    --Get System date
    SELECT TOP 1 @SystemDate = CONVERT(DATETIME, SystemDate, 112) FROM [System] WITH (NOLOCK)
    --Get Main reservation and last hall line departure date
    SELECT TOP 1 @MainResDepartureDate = CONVERT(DATETIME, Reservations.DepartureDate, 112) 
    , @LastHallDepartureDate = CONVERT(DATETIME, ReservationConferenceLine.DepartureDate, 112) 
    FROM Reservations WITH (NOLOCK) 
      INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
    WHERE Reservations.ReferenceNumber= @ReferenceNumber AND Reservations.GuestNumber=0 
      --nb20060405 only on lines with product or package or Price > 0
      AND (ISNULL(ReservationConferenceLine.ProductNumber, 0) < 0 OR ISNULL(ReservationConferenceLine.PackageNumber,0) > 0 OR ISNULL(ReservationConferenceLine.Price,0) <> 0)
    ORDER BY ReservationConferenceLine.DepartureDate
    --If all dates match the current date 
    IF DATEDIFF(DAY, @SystemDate, @CurrentDate) = 0 AND DATEDIFF(DAY, @MainResDepartureDate, @CurrentDate) = 0 AND DATEDIFF(DAY, @LastHallDepartureDate, @CurrentDate) = 0
    BEGIN
      SELECT @Return = (CASE WHEN COUNT(*) > 0 THEN 0 ELSE 1 END), @cnt = COUNT(*)
      FROM Transactions WITH (NOLOCK)
        INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber 
        INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = Transactions.UserIdNumber 
        INNER JOIN Reservations WITH (NOLOCK) ON Folios.ReferenceNumber = Reservations.ReferenceNumber 
          AND Reservations.GuestNumber = 0
        INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber 
          AND ReservationConferenceLine.ProductNumber = Transactions.ProductNumber
          AND (MovedDeleted=0 OR (MovedDeleted=1 AND MovedFolioNr>0)) AND PERSONEL.UserTypeNumber = 3 
      WHERE Folios.ReferenceNumber = @ReferenceNumber AND Folios.GuestNumber = @GuestNumber 
        AND DATEDIFF(DAY, Transactions.OriginalDate, @CurrentDate)=0  

      IF @cnt = 0
        SELECT @Return = (CASE WHEN COUNT(*) > 0 THEN 0 ELSE 1 END), @cnt = COUNT(*)
        FROM Transactions WITH (NOLOCK)
          INNER JOIN FolioRestResTableChairsXTab WITH (NOLOCK) ON FolioRestResTableChairsXTab.FolioNumber = Transactions.FolioNumber
            AND (MovedDeleted=0 OR (MovedDeleted=1 AND MovedFolioNr>0))
          INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON FolioRestResTableChairsXTab.RestaurantReservationNumber = ReservationConferenceLine.ReservationConfLineNumber
            AND ReservationConferenceLine.ProductNumber = Transactions.ProductNumber
            AND ReservationConferenceLine.ReferenceNumber = @ReferenceNumber
          INNER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationConferenceLine.ReferenceNumber 
            AND Reservations.ReferenceNumber = @ReferenceNumber  
            AND Reservations.GuestNumber = @GuestNumber
          INNER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = Transactions.FolioNumber
            AND Folios.FolioNumber = FolioRestResTableChairsXTab.FolioNumber
          INNER JOIN Personel WITH (NOLOCK) ON Personel.UserIdNumber = Transactions.UserIdNumber 
            AND PERSONEL.UserTypeNumber = 3 
        WHERE DATEDIFF(DAY, Transactions.OriginalDate, @CurrentDate)=0         
    END
  END
  RETURN @Return
END