
CREATE PROCEDURE [dbo].[OnlineWareStock] @Hotelnumber INT, @Onlinebooktypenumber INT, @StartDate DATETIME, @EndDate DATETIME  AS
SET NOCOUNT ON
/*
    Created by Troels Buch Maj 2011

    Changes: LJE20120119, CurrentStock is not allowed to return NULL - return 0, IsNull(Ware.CurrentStock,0) CurrentStock.
*/

SELECT distinct ArrangementLines.ArrangementLineNumber,ArrangementLines.[Date],ArrangementLines.Time, ArrangementLines.DepartureTime, ArrangementLines.Pieces,ArrangementLines.WareNumber,ArrangementReservationsXtab.ReferenceNumber,
WareGroups.WareGroupNumber,Ware.WareLinkNumber,Ware.Payment
  , IsNull(Ware.CurrentStock,0) CurrentStock
FROM  ArrangementReservationsXtab (nolock) LEFT OUTER JOIN
RESERVATIONS (NOLOCK) ON ArrangementReservationsXtab.ReferenceNumber = RESERVATIONS.ReferenceNumber
AND ArrangementReservationsXtab.GuestNumber = RESERVATIONS.GuestNumber
LEFT OUTER JOIN ArrangementLines (nolock) ON
ArrangementReservationsXtab.ArrangementLineNumber = ArrangementLines.ArrangementLineNumber INNER JOIN
Ware(nolock) ON ArrangementLines.WareNumber = Ware.WareNumber LEFT OUTER JOIN
WareGroups (nolock) ON Ware.WareGroupNumber = WareGroups.WareGroupNumber
INNER JOIN ReservationMain (nolock) on ReservationMain.ReferenceNumber = Reservations.Referencenumber
AND ArrangementLines.Date>=@StartDate AND Arrangementlines.Date<=@EndDate AND Status <>7 AND Status <> 8
AND ArrangementLines.WareNumber IN(
SELECT DISTINCT Ware.WareNumber
FROM Ware (NOLOCK) 
INNER JOIN WareDepartmentsXtab wdx (NOLOCK) ON wdx.WareNumber = Ware.WareNumber AND wdx.Type = 1 -- Cashier department
INNER JOIN Departments d (NOLOCK) ON d.PK_Department = wdx.FK_Department AND ( (@Hotelnumber > -1 AND d.HotelNumber = @Hotelnumber)  OR (@Hotelnumber = -1)) -- AV 2009 0420
INNER JOIN OnlineWareDelivery (NOLOCK) ON Ware.Warenumber = OnlineWareDelivery.Warenumber 
INNER JOIN OnlineBookTypeWareXTab (NOLOCK) ON OnlineBookTypeWareXTab.Warenumber=OnlineWareDelivery.Warenumber 
INNER JOIN OnlineBookType (NOLOCK) ON OnlineBookType.OnlineBookTypeNumber = OnlineBookTypeWareXTab.OnlineBookTypeNumber 
WHERE allowfilterpicassoonline=1   
AND OnlineBookType.OnlineBookTypeNumber = (SELECT OnlineBookTypeNumber FROM OnlineBookType WHERE Number=@Onlinebooktypenumber)
AND IsNull(Ware.MarkAsDeleted, 0)=0) 
ORDER BY ArrangementLines.WareNumber
GO

