CREATE PROCEDURE [dbo].[TransactionDecorate] @WareNumber INT, @ProductNumber INT, @BillTypeNumber SmallInt, @DateStamp DATETIME, @OriginalDate DATETIME
, @GuestNumber SMALLINT, @RoomTypeSortNumber INT, @RoomTypeNumber INT, @Note NVARCHAR(50), @TotalPrice FLOAT, @Vat DECIMAL(19,4), @PriceExclVAT DECIMAL(19,4)
, @Pieces INT, @SinglePrice MONEY, @Payment SMALLINT, @InternalCourseProduction BIT, @WaregroupPayment TINYINT, @BillLayoutOrderByDate BIT, @SumTableCreated BIT
, @SortTransByOriginalDateOnBill BIT, @LocaleId INT
, @OrderWGRL INT OUTPUT, @OrderWRL INT OUTPUT, @SortOrderNumber INT OUTPUT, @SortPeriodNumber INT OUTPUT, @DoInsert TINYINT OUTPUT, @TextOverwrite NVARCHAR(250) OUTPUT
AS
SET NOCOUNT ON
--KJ20200603: Bug: Fix for "Cannot insert the value NULL into column SortPeriodNumber"
--nb20180817 Created
/*Used fields
ProductNumber, DateStamp, OriginalDate, WareNumber, Pieces, Price, Note, TotalPrice, Vat, PriceExclVAT 
*/

DECLARE @DoSort BIT
, @GroupIndex INT
, @SortSQL NVARCHAR(700)
, @SumNo INT
, @SortText NVARCHAR(50)
, @SortTextOverwrite BIT
, @ShowPrint BIT
, @LTextOverwrite NVARCHAR(250)
, @RowCount INT
, @OrderWGRLOrig INT
, @OrderWRLOrig INT

SET @ShowPrint = 1
SET @OrderWGRLOrig = @OrderWGRL
SET @OrderWRLOrig = @OrderWRL
SET @DoSort= 0
SET @DoInsert = 0
SET @Note = ISNULL(@Note, '')
SET @LTextOverwrite = @TextOverwrite

SELECT @GroupIndex=ISNULL([GroupIndex], 0)
FROM WareGroupBillSortTypesXTab (NOLOCK) 
  INNER JOIN Ware (NOLOCK) ON WareGroupBillSortTypesXTab.WaregroupNumber = Ware.WareGroupNumber
WHERE (WareGroupBillSortTypesXTab.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
--nb20111102 On summerized overnight wares (Charge, charge product, null product and post days methods)insure EU spec and overnight vat split are correct 
IF @GroupIndex = 0 and @ProductNumber <> -2
  IF ISNULL(@ProductNumber, 0) > 0 
    SET @SortSQL = ' WHERE ProductNumber > 0 '
  ELSE IF @ProductNumber IS NULL
    SET @SortSQL = ' WHERE ProductNumber IS NULL '
  ELSE
    SET @SortSQL = ' WHERE ProductNumber='+Convert(NVARCHAR,@ProductNumber)+' '
ELSE
  SET @SortSQL = ' WHERE 1=1'
IF @BillLayoutOrderByDate = 1
  IF @SortTransByOriginalDateOnBill = 0
    SET @SortSQL = @SortSQL + ' AND Convert(NVARCHAR,DateStamp,112)='''+Convert(NVARCHAR,@DateStamp,112)+''' ' 
  ELSE
    SET @SortSQL = @SortSQL + ' AND Convert(NVARCHAR,OriginalDate,112)='''+Convert(NVARCHAR,@OriginalDate,112)+''' ' 
SELECT @SumNo=SortTypeNumber, @SortOrderNumber=ISNULL(FKBillSortOrder, 0), @SortPeriodNumber=ISNULL(FKBillSortPeriod,0), @SortText=ISNULL(L.Name, Text), @OrderWGRL=OrderNumber
, @SortTextOverwrite=TextOverwrite, @GroupIndex=ISNULL([GroupIndex], 0)
FROM dbo.WareGroupBillSortTypesXTab RLW WITH (NOLOCK) 
  JOIN dbo.Ware WITH (NOLOCK) ON RLW.WaregroupNumber = Ware.WareGroupNumber
  LEFT JOIN dbo.Translation L (NOLOCK) ON L.ObjectNumber = RLW.ItemId AND L.CountryNumber = @LocaleId AND L.Type = 17 --ttReceiptLayoutWaregroup
WHERE (RLW.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
SET @RowCount = @@RowCount
  IF @ShowPrint = 1 --and @SortTypeNumber = 2
  BEGIN
    PRINT 'DEBUG Receipt Layout start ----------------------------'
    PRINT '@WareNumber = ' + CONVERT(NVARCHAR, @WareNumber)
    PRINT '@BillTypeNumber = ' + CONVERT(NVARCHAR, @BillTypeNumber)
    PRINT '@Note = ' + CONVERT(NVARCHAR, @Note)
    PRINT '@DoSort=' + CONVERT(NVARCHAR, @DoSort)
    PRINT 'WaregroupDef exists start ----'
    PRINT '@SumNo=' + CONVERT(NVARCHAR, @SumNo)
    PRINT '@ProductNumber = ' + CONVERT(NVARCHAR, ISNULL(@ProductNumber, 0))
    PRINT '@ReceiptLayoutOrderByDate = ' + CONVERT(NVARCHAR, @BillLayoutOrderByDate)
    PRINT '@WareGroupSortNumberOnBill = ' + CONVERT(NVARCHAR, @OrderWGRL)
    PRINT 'WaregroupDef exists end ----'
  END
IF @RowCount > 0
BEGIN
  SET @OrderWGRL = ISNULL(@OrderWGRL, @OrderWGRLOrig)
  IF @SumNo = 1 AND @GroupIndex <= 0 --if Specified try to get the ware sort setup              
  BEGIN
    SELECT @SumNo=SortTypeNumber, @SortOrderNumber=FKBillSortOrder, @SortPeriodNumber=FKBillSortPeriod, @SortText=ISNULL(L.Name, Text)
	  , @OrderWRL=OrderNumber, @SortTextOverwrite=TextOverwrite --mli TextOverwrite is used to fill BillText with the new text
    FROM dbo.WareBillSortTypesXTab RLW WITH (NOLOCK) 
	    JOIN dbo.Ware WITH (NOLOCK) ON RLW.WareNumber = Ware.WareNumber
      LEFT JOIN dbo.Translation L (NOLOCK) ON L.ObjectNumber = RLW.ItemId AND L.CountryNumber = @LocaleId AND L.Type = 16 --ttReceiptLayoutWare
    WHERE (RLW.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
    SET @RowCount = @@RowCount
    SET @OrderWRL = ISNULL(@OrderWRL, @OrderWRLOrig)
    IF @ShowPrint = 1 --and @SortTypeNumber = 2
      PRINT '@RowCount = ' + CONVERT(NVARCHAR, @RowCount)

    IF @RowCount = 0 --Waregroup specified sort setup used
    BEGIN
      SELECT @SumNo=SortTypeNumber, @SortOrderNumber=FKBillSortOrder, @SortPeriodNumber=FKBillSortPeriod, @SortText=ISNULL(L.Name, Text), @SortTextOverwrite=TextOverwrite
      FROM dbo.WareGroupBillSortTypesXTab RLW WITH (NOLOCK) 
	      JOIN dbo.Ware WITH (NOLOCK) ON RLW.WaregroupNumber = Ware.WareGroupNumber
        LEFT JOIN dbo.Translation L (NOLOCK) ON L.ObjectNumber = RLW.ItemId AND L.CountryNumber = @LocaleId AND L.Type = 17 --ttReceiptLayoutWaregroup
      WHERE (RLW.BillTypeNumber = @BillTypeNumber) AND (Ware.WareNumber = @WareNumber)
      -- Waregroup sort type= Specified and undefined ware sorttype = Specified => Transaction Specified
      SET @DoSort= 0
    END
    ELSE
    BEGIN
      SET @SortSQL = @SortSQL +' AND WareNumber='+Convert(NVARCHAR,@WareNumber)+' '
      IF @SumNo = 1
        SET @DoSort= 0
      ELSE IF @SumNo > 0
        SET @DoSort= 1
      ELSE
        SET @DoSort= 0
    END
  END
  ELSE IF @GroupIndex > 0 --nb20050523
  BEGIN
    SET @DoSort= 1
    SET @SortSQL = @SortSQL +' AND WareNumber IN (SELECT DISTINCT WareNumber FROM Ware WITH (NOLOCK)'
    +' INNER JOIN WAREGROUPS WITH (NOLOCK) ON Ware.WareGroupNumber = Waregroups.WaregroupNumber'
    +' INNER JOIN WaregroupBillSortTypesXtab WBT WITH (NOLOCK) ON WBT.WaregroupNumber = Waregroups.WaregroupNumber AND WBT.[GroupIndex] = ' 
	+ Convert(NVARCHAR, @GroupIndex) + ' AND WBT.BillTypeNumber= '+CONVERT(NVARCHAR, ISNULL(@BillTypeNumber, 0))+') '
  END
  ELSE
  BEGIN
    SET @DoSort= 1              
    SET @SortSQL = @SortSQL +' AND WareNumber In (SELECT DISTINCT WareNumber FROM Ware WITH (NOLOCK)'
    +' INNER JOIN WAREGROUPS WITH (NOLOCK) ON Ware.WareGroupNumber = (SELECT WaregroupNumber FROM Ware WITH (NOLOCK) where WareNumber = ' + Convert(NVARCHAR, @WareNumber) + ')) '
  END
  IF @GroupIndex > 0  --nb20050523 find the first waregroup in group
  BEGIN
    SELECT TOP 1 @SortPeriodNumber=FKBillSortPeriod, @SortText=WareGroupBillSortTypesXTab.Text, @SortTextOverwrite=TextOverwrite, @OrderWGRL=OrderNumber, @TextOverwrite=Waregroups.Text
    FROM WareGroupBillSortTypesXTab WITH (NOLOCK) INNER JOIN Waregroups WITH (NOLOCK) ON Waregroups.WaregroupNumber= WareGroupBillSortTypesXTab.WaregroupNumber
    WHERE WareGroupBillSortTypesXTab.BillTypeNumber = @BillTypeNumber and WareGroupBillSortTypesXTab.[GroupIndex] = @GroupIndex 
    ORDER BY WareGroupBillSortTypesXTab.OrderNumber   
    SET @OrderWGRL = ISNULL(@OrderWGRL, @OrderWGRLOrig)
  END
  IF @SortTextOverwrite = 1 SET @TextOverwrite=@SortText
  IF ISNULL(@BillLayoutOrderByDate, 0) = 0 and @SortPeriodNumber = 2 
    SET @SortSQL = @SortSQL + ' AND Convert(NVARCHAR,DateStamp,112)='''+Convert(NVARCHAR,@DateStamp,112)+''' '             
END
IF ISNULL(@SortOrderNumber,0) = 0 
  SET @SortOrderNumber=0
IF ISNULL(@GroupIndex, 0) = 0 AND @SortOrderNumber > 0 
BEGIN
  IF (@SortOrderNumber=1) 
    SET @SortOrderNumber= Convert(int, @GuestNumber)
  ELSE IF (@SortOrderNumber=2) AND @Note<>''
    SET @SortOrderNumber=Convert(int, @Note)
  ELSE IF (@SortOrderNumber=3)
    SET @SortOrderNumber=@RoomTypeSortNumber
END
IF @ShowPrint = 1
BEGIN
  PRINT '@SumNo=' + CONVERT(NVARCHAR, @SumNo)
  PRINT '@BillLayoutOrderByDate=' + ISNULL(CONVERT(NVARCHAR, @BillLayoutOrderByDate), '');
  PRINT '@SortPeriodNumber=' + CONVERT(NVARCHAR, @SortPeriodNumber)
  PRINT '@SortOrderNumber = ' + CONVERT(NVARCHAR, @SortOrderNumber)
  PRINT '@WareGroupSortNumberOnBill=' + CONVERT(NVARCHAR, @OrderWGRL)
  PRINT '@SortSQL=' + CONVERT(NVARCHAR(400), @SortSQL)
  --PRINT '@SortTextOverwrite=' + CONVERT(NVARCHAR, @SortTextOverwrite)
  --PRINT '@DoSort = ' + CONVERT(NVARCHAR, @DoSort)
END  
IF @DoSort=0
  SET @DoInsert = 1
ELSE IF @GroupIndex > 0
BEGIN
  SET @SortSQL= 'update #Transaction SET Pieces=1'
  +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@TotalPrice,2)+' THEN '+Convert(NVARCHAR,@TotalPrice,2)+' ELSE NULL END'
  +', Note=CASE WHEN RTRIM(Note)='''+RTRIM(@Note)+''' THEN Note ELSE NULL END'
  +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
  +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
  +', PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
  IF @PRoductNumber = - 2 --(Kitchen Layout)
	SET @SortSQL= @SortSQL+' AND Note='''+LTRIM(RTRIM(Convert(NVARCHAR, @Note)))+''''
  EXEC (@SortSQL)
  IF @@ROWCOUNT = 0
    SET @DoInsert=1
END
ELSE
BEGIN
  IF @SumNo = 2 or @ProductNumber = -2 --and @SortTypeNumber = 6 --joined (align 4 Kitchen with price)
  BEGIN
    SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Convert(NVARCHAR,@Pieces)
    +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@SinglePrice,2)+' THEN '+Convert(NVARCHAR,@SinglePrice,2)+' ELSE NULL END'                 
    +', Note=CASE WHEN RTRIM(Note)='''+LTRIM(RTRIM(@Note))+''' THEN Note ELSE NULL END'
    +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2) +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
    +', PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
	IF @ProductNumber = - 2 --(Kitchen Layout)
	  SET @SortSQL= @SortSQL+' AND Note='''+LTRIM(RTRIM(@Note))+''''
    EXEC (@SortSQL)
    IF @@ROWCOUNT = 0
      SET @DoInsert=1
  END
  ELSE IF @SumNo = 3 --person / guest
  BEGIN
    IF (@Payment>0)
      SET @SortSQL= 'update #Transaction SET Pieces=1'
      +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@TotalPrice,2)+' THEN '+Convert(NVARCHAR,@TotalPrice,2)+' ELSE NULL END'
      +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
      +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
      +', PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
      +' AND GuestNumber=''' + Convert(NVARCHAR, @GuestNumber)+'''' 
    ELSE
      SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Convert(NVARCHAR,@Pieces)
      +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@SinglePrice,2)+' THEN '+Convert(NVARCHAR,@SinglePrice,2)+' ELSE NULL END'
      +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
      +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
      +',PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
      +' AND GuestNumber=''' + Convert(NVARCHAR, @GuestNumber)+'''' 
    EXEC (@SortSQL)
    IF @@ROWCOUNT = 0
      SET @DoInsert=2
  END
  ELSE IF @SumNo = 4 --roomnumber
  BEGIN
    SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Convert(NVARCHAR,@Pieces)
    +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@SinglePrice,2)+' THEN '+Convert(NVARCHAR,@SinglePrice,2)+' ELSE NULL END'
    +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
    +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
    +',PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL+' AND Note='''+LTRIM(RTrim(@Note))+'''' 
    --print @SortSQL
    EXEC (@SortSQL)
    IF @@ROWCOUNT = 0
      SET @DoInsert=1
  END
  ELSE IF @SumNo = 5 --roomtype
  BEGIN
    SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Convert(NVARCHAR,@Pieces)
    +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@SinglePrice,2)+' THEN '+Convert(NVARCHAR,@SinglePrice,2)+' ELSE NULL END'
    +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
    +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
    +',PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
    +' AND #Transaction.note IN (SELECT Rooms.Number FROM RoomTypes WITH (NOLOCK)'
    +' INNER JOIN ROOMS WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = ROOMS.RoomTypeNumber WHERE RoomTypes.RoomTypeNumber=''' + Convert(NVARCHAR, @RoomTypeNumber) + ''')'
    EXEC (@SortSQL)
    IF @@ROWCOUNT = 0
      SET @DoInsert=1
  END
  ELSE IF @SumNo = 6 --price
  BEGIN
    IF @SumTableCreated = 1 AND (@InternalCourseProduction = 0 AND NOT @WaregroupPayment = 3 OR 
      @InternalCourseProduction = 1 AND (NOT @WaregroupPayment in (3, 8) OR @SortPeriodNumber = 1))
    BEGIN
      IF @InternalCourseProduction = 0 AND @WaregroupPayment = 2 OR 
        @InternalCourseProduction = 1 AND @WaregroupPayment in (2, 3, 8)
        SET @SortSQL= 'update #Transaction_Sort SET Pieces=1'
        +', Price=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
        +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
        +', Note=CASE WHEN RTRIM(Note)='''+LTRIM(RTRIM(@Note))+''' THEN Note ELSE NULL END'
		+', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
		+', PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
		+' AND GuestNumber=''' + Convert(NVARCHAR, @GuestNumber)+''''
      ELSE
        SET @SortSQL= 'update #Transaction_Sort SET Pieces=Pieces+'+Convert(NVARCHAR,@Pieces)
        +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2)
		+', Note=CASE WHEN RTRIM(Note)='''+LTRIM(RTRIM(@Note))+''' THEN Note ELSE NULL END'
		+', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
		+', PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
		+' AND GuestNumber=''' + Convert(NVARCHAR, @GuestNumber)+''' AND ABS(Price)='+Convert(NVARCHAR,ABS(@SinglePrice),2)
      END
    ELSE
      SET @SortSQL=''
    EXEC (@SortSQL)
    IF @@ROWCOUNT = 0 or @SortSQL=''
      SET @DoInsert=3
  END
  ELSE IF @SumNo = 7 --Line text
  BEGIN
    SET @SortSQL= 'update #Transaction SET Pieces=Pieces+'+Convert(NVARCHAR,@Pieces)
    +', Price= CASE WHEN Price ='+Convert(NVARCHAR,@SinglePrice,2)+' THEN '+Convert(NVARCHAR,@SinglePrice,2)+' ELSE NULL END'                 
    +', Note=CASE WHEN RTRIM(Note)='''+LTRIM(RTRIM(@Note))+''' THEN Note ELSE NULL END'
    +', TotalPrice=TotalPrice+'+Convert(NVARCHAR,@TotalPrice,2) +', Vat=Vat+'+Convert(NVARCHAR,@Vat,2)
    +', PriceExclVAT=PriceExclVAT+'+Convert(NVARCHAR,@PriceExclVAT,2)+@SortSQL
    +' AND BillText='''+LTRIM(RTRIM(@LTextOverwrite))+''''
    EXEC (@SortSQL)
    IF @@ROWCOUNT = 0
      SET @DoInsert=1
  END
IF @ShowPrint = 1
BEGIN
  --PRINT '@SortTextOverwrite=' + CONVERT(NVARCHAR, @SortTextOverwrite)
  --PRINT '@DoSort = ' + CONVERT(NVARCHAR, @DoSort)
  PRINT '@SortSQL = ' + @SortSQL
  PRINT '@DoInsert = ' + CONVERT(NVARCHAR, @DoInsert)
  PRINT 'DEBUG Receipt Layout end ----------------------------'
END  
END