
CREATE PROCEDURE [dbo].[OnlineProductByPeriode] @AStart DATETIME
, @AEnd DATETIME
, @AProducttypeNumber INT = 0  -- if > 0 find all products matching typenumber.
, @AProductNumber INT = 0  -- or if > 0 find exactly one product matching number.
AS
SET NOCOUNT ON
/*
    Created by Troels Buch Jan 2009
    
    Changes: 
    TB20131213: Added SortNumber
    LJE 20111216: AProductNumber added.
    LJE 20111215: Also get Child1MinAgeInMonths, Child1MaxAgeInMonths,... in selection.
    TB20070821 - Find MaxPaxTotal from OnlineRoomTypes

    NOTES: 	- 
*/

SELECT p.ProductNumber
,pt.ProductTypeNumber
,p.ProductName
,p.Description
,p.ProductDate
,p.ProductEndDate
,p.MiniMumDays
,p.MaximumDays
,p.ArrivalMonday
,p.ArrivalTuesday
,p.ArrivalWednesDay
,p.ArrivalThursday
,p.ArrivalFriday
,p.ArrivalSaturday
,p.ArrivalSunday
,pt.SortNumber
,ISNULL(pe.Child1MinAgeInMonths,-1) Child1MinAgeInMonths
,ISNULL(pe.Child1MaxAgeInMonths,-1) Child1MaxAgeInMonths 
,ISNULL(pe.Child2MinAgeInMonths,-1) Child2MinAgeInMonths
,ISNULL(pe.Child2MaxAgeInMonths,-1) Child2MaxAgeInMonths
FROM Products p(NOLOCK) 
INNER JOIN ProductTypes pt (NOLOCK) ON p.ProductTypeNumber = pt.ProductTypeNumber  
INNER JOIN ProductExtended pe ON p.ProductNumber = pe.FK_ProductNumber

WHERE AllowFilterPicassoOnline=1 AND p.DayProduct = 0
AND CONVERT(DATETIME, ProductDate)<=CONVERT(CHAR(8), @AStart, 112)
AND ProductEndDate>= CONVERT(CHAR(8), @AEnd, 112)
--AND IsNull(MiniMumDays, 0)<=1 
--AND IsNull(MaximumDays, 0)>=1
AND IsNull(CustomerRate, 0) = 0
--AND p.ProductTypenumber = @AProducttypeNumber
AND ((@AProductNumber > 0 AND p.ProductNumber = @AProductNumber) OR (@AProductNumber = 0))
AND ((@AProducttypeNumber > 0 AND p.ProductTypeNumber = @AProducttypeNumber) OR (@AProducttypeNumber = 0))


/*SELECT * FROM Products (NOLOCK) WHERE AllowFilterPicassoOnline=1 AND Products.DayProduct = 0
AND CONVERT(DATETIME, ProductDate)<= CONVERT(CHAR(8), @AStart, 112)--'20090201' 
AND ProductEndDate>= CONVERT(CHAR(8), @AEnd, 112)--'20090228' 
AND IsNull(MiniMumDays, 0)<=1 
AND IsNull(MaximumDays, 0)>=1
AND IsNull(CustomerRate, 0) = 0*/

GO

