CREATE PROCEDURE [dbo].[OnlineFindWares]  @AHotelNumber INT,  @AWareNumber INT, @ALangSameAsPicasso BIT, @ACountryNumber INT  AS
SET NOCOUNT ON
/*
   Created by Troels Buch Juni 2007

   Changes:
    SG 2018-08-31: Added support for new fields IsPlaceTextNeeded and PlaceTextDescription
    TBU20150120: Added IsNull to Duration
    TBU20150116: Added Duration
    TB20110125: Added check for MarkAsDeleted
	TB20110107: Added DeliveryChargeType, DeliveryChargeTypeText,
	TB20100421: Added check on ORDER BY
	TB20090714: Moved Sortnumber
	TB20090707: Added Sortnumber
    AV 2009 0420 only use hotelnumber if it is bigger than -1

   	NOTES: 	
*/

DECLARE @UseTranslation BIT
DECLARE @OnlineBookTypeNumber INT
DECLARE @HallActiovate BIT
DECLARE @Ware1stSortSortnumber BIT

SELECT @UseTranslation = keyvalue FROM HotelSetup WITH (NOLOCK) WHERE searchkey = 'OnlineUseTranslation'
SELECT @HallActiovate = keyvalue FROM HotelSetup WITH (NOLOCK) WHERE searchkey = 'OnlineHallActivate'
SET @OnlineBookTypeNumber = @AWareNumber
IF EXISTS(SELECT KeyValue FROM HOTELSETUP WHERE SEARCHKEY = 'OnlineWare1stSortSortnumber')
	SELECT @Ware1stSortSortnumber = keyvalue FROM HotelSetup WITH (NOLOCK) WHERE searchkey = 'OnlineWare1stSortSortnumber'
ELSE
	SET @Ware1stSortSortnumber = 0

--SELECT @OnlineBookTypeNumber = Onlinebooktypenumber FROM OnlineBookType (NOLOCK) WHERE Number= @AWareNumber
--SET SELECT OnlineBookTypeNumber FROM OnlineBookType  (NOLOCK) WHERE Number= @AWareNumber
--select * FROM OnlineBookType (NOLOCK) WHERE Number= @AWareNumber
print 'tb'
--print @UseTranslation
--print @HallActiovate
print @AWareNumber
print @OnlineBookTypeNumber
print @ACountryNumber

IF @UseTranslation = 0 OR @ALangSameAsPicasso = 1
BEGIN
print 'Not use trans'

  SELECT DISTINCT Ware.WareNumber, WareName, Price, IsNull(DefaultArrangementTime, '00:00') AS DefaultArrangementTime, 
  ISNULL(MaxPieces,0) AS MaxPieces, DeliveryType, DeliveryChargeType, DeliveryChargeTypeText, IsNull(Duration, 30) AS Duration, CONVERT(VARCHAR(8000), Note) AS Note, Ware.Sortnumber,
  CASE WHEN @Ware1stSortSortnumber = 0 THEN DeliveryType END AS tmp,
  IsNull(IsPlaceTextNeeded, 0) as IsPlaceTextNeeded, IsNull(PlaceTextDescription, '') as PlaceTextDescription
  FROM Ware WITH (NOLOCK) 
  INNER JOIN WareDepartmentsXtab wdx WITH (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 d.HotelNumber = @AHotelNumber
  INNER JOIN Departments d WITH (NOLOCK) ON d.PK_Department = wdx.FK_Department AND ( (@AHotelNumber > -1 AND d.HotelNumber = @AHotelNumber)  OR (@AHotelNumber = -1)) -- AV 2009 0420
  INNER JOIN OnlineWareDelivery WITH (NOLOCK) ON Ware.Warenumber = OnlineWareDelivery.Warenumber 
  INNER JOIN OnlineBookTypeWareXTab WITH (NOLOCK) ON OnlineBookTypeWareXTab.Warenumber=OnlineWareDelivery.Warenumber 
  INNER JOIN OnlineBookType WITH (NOLOCK) ON OnlineBookType.OnlineBookTypeNumber = OnlineBookTypeWareXTab.OnlineBookTypeNumber 
  WHERE allowfilterpicassoonline=1 
  AND IsNull(Ware.MarkAsDeleted, 0 ) = 0
  --AND dbo.isdepartmentinhotellist(ware.department, '2', 1)=1 
  AND OnlineBookType.OnlineBookTypeNumber = @OnlineBookTypeNumber
  ORDER BY
  CASE WHEN @Ware1stSortSortnumber = 0 THEN DeliveryType END
  ,Ware.Sortnumber, Ware.WareNumber
END
ELSE
BEGIN
print 'use trans'

  SELECT DISTINCT Ware.WareNumber, WareName, Price, IsNull(DefaultArrangementTime, '00:00') AS DefaultArrangementTime, 
  IsNull(MaxPieces,0) AS MaxPieces, DeliveryType, DeliveryChargeType, DeliveryChargeTypeText, IsNull(Duration, 30) AS Duration, CONVERT(VARCHAR(8000), Note) AS Note, translation.Name as WareNameTrans, Translation.Description1, Ware.Sortnumber,
  CASE WHEN @Ware1stSortSortnumber = 0 THEN DeliveryType END AS tmp,
  IsNull(IsPlaceTextNeeded, 0) as IsPlaceTextNeeded, IsNull(Description3, '') as PlaceTextDescription
  FROM Ware WITH (NOLOCK) 
  INNER JOIN WareDepartmentsXtab wdx WITH (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 d.HotelNumber = @AHotelNumber
  INNER JOIN Departments d WITH (NOLOCK) ON d.PK_Department = wdx.FK_Department AND ( (@AHotelNumber > -1 AND d.HotelNumber = @AHotelNumber)  OR (@AHotelNumber = -1)) -- AV 2009 0420
  INNER JOIN OnlineWareDelivery WITH (NOLOCK) ON Ware.Warenumber = OnlineWareDelivery.Warenumber 
  INNER JOIN OnlineBookTypeWareXTab WITH (NOLOCK) ON OnlineBookTypeWareXTab.Warenumber=OnlineWareDelivery.Warenumber 
  INNER JOIN OnlineBookType WITH (NOLOCK) ON OnlineBookType.OnlineBookTypeNumber = OnlineBookTypeWareXTab.OnlineBookTypeNumber 
  INNER JOIN Translation WITH (nolock) on Translation.ObjectNumber = Ware.Warenumber and Translation.Type = 1 --ware
  WHERE allowfilterpicassoonline = 1 
  AND IsNull(Ware.MarkAsDeleted, 0 ) = 0
  and Translation.Countrynumber = @ACountryNumber
  and OnlineBookType.OnlineBookTypeNumber = @OnlineBookTypeNumber
  ORDER BY
  CASE WHEN @Ware1stSortSortnumber = 0 THEN DeliveryType END
  ,Ware.Sortnumber, Ware.WareNumber

END
