CREATE PROCEDURE DBO.PostProduct @DayProduct bit, @ProductNumber int, @ProductWareNumber int, @PackageNumber int, @ReferenceNumber int, @GuestNumber int, @FolioNumber int, @ProductPriceDate DateTime, @DateType TinyInt, @Pieces int, @PriceOverWritten bit, @Price money, @Adults int, @Child1 tinyint, @Child2 tinyint, @RoomTypeNumber int, @BillText varchar(50), @Note varchar(50), @UserIDNumber int, /*@TurnoverDepartment Char(1)*/ @FK_TurnoverDepartment INT, @CountryNumber int, @HotelCustomerNumber int , @DiscountRate float = 0, @PackageNumber2 INT, @PackageNumber3 INT, @ACashDesk INT = -1, @AChargeOnSystemDate BIT = 1 AS
SET NOCOUNT ON

/*
  Changes:
    KJ 20140611: Ext: CashDesk per hotel. Case #CR073269
    KJ 20121219: Bug: Fix for "TIME is not a defined system type". Case #CR051065
    KJ 20121217: Ext: Added @AChargeOnSystemDate Req #1405
    KJ 20121217: Ext: Removed the get reference/guest from folionumber Req #1405 
    KJ 20110325: Bug: Fix for "Stay+departure package not charged if no arrival package on product" Case# CR021964
    KJ 20110211: Bug: Fix for "Price/Pieces wrong when number of rooms is less than 0" Case# CR018253 + CR019552 and other
    KJ 20091207: Ext: CashDesk
    KJ 20090326: Ext: Enable post of 3 packages on one day.
    KJ 20081222: Ext: Added OriginalGlobalTransactionNumber 
    KJ 20080805: Bug: Fix for "Package not posted with correct roomtypenumber"
    KJ 20070411: Department rewrite
    KJ 20061211: Added @PostAllLines = 1 to call to PostPackage.
    JP20050526: changed to use GetNextNumber
    Rettet d. 8/4-2005 : imp. Discountrate to charge package if @DiscountAsNettoPrice
    Rettet d. 9/2-2005 : imp. @Child2 when dayprodutc
    Rettet d. 13/10-2004 : if dayproduct and charge product set priceoverwrite=1
    Rettet d. 14/05-2004 : Imp. HotelcustoemrNumber for multihotel(s)
    Rettet d. 25/02-2004 : BugFix : If packageturnover > productprice
    Rettet d. 03/02-2004 : changed department to char(1)
    Rettet d. 07/01-2004 : imp. new postpackage
    Rettet d. 11/12-2003 : added ABS to vatoflogi if < 0
    Rettet d. 04/09-2003 : remove excl/vat from transtext
    Rettet d. 14/08-2003 : imp. 2 decimals
    Rettet d. 06/05-2003 : imp. new parameter for procedure postpackage
    Rettet d. 10/04-2003 : Fixed vat problems and - (negativ) pieces
    Rettet d. 22/11-2002 : Changed if @VatOfLogi*Pieces>=0  ==>> if @VatOfLogi>=0  (hvis både vatoflogi and pieces er negative)
    Rettet d. 02/9-2002 : use convert(float) when VatOfPrice
    Rettet d. 22/8-2002 : charge only if (@Logi<>0 OR @VatOfLogi<>0 OR @ProductPrice)
*/

Declare @CurrentDateStr char(8)

Declare @SinglePriceToPackage money
Declare @SinglePriceToTrans money
Declare @DiscountPrice money

Declare @SplitBillNumber int

Declare @StayStartDate DATETIME
Declare @StayEndDate DATETIME

Declare @AdultAddon Money
Declare @Children1Addon Money
Declare @Children2Addon Money

Declare @AddOn money

Declare @IsFixedPrice bit

Declare @AdultPrice Money
Declare @Children1Price Money
Declare @Children2Price Money

Declare @ProductPrice Money

Declare @PackagePriceProduct money
Declare @VatByPacketValueProduct money
DECLARE @tmpPackagePriceProduct MONEY
DECLARE @tmpVatByPacketValueProduct MONEY

Declare @ResultAccountNumber int
Declare @VATAccountNumber int
Declare @BalanceAccountNumber int

Declare @VatValueProduct Float
Declare @VatValueNumber tinyInt

Declare @AddressType SmallInt

Declare @DummyTransNrShowOnBillOnAdd int

Declare @DiscountwareText varchar(50)
Declare @TempStr varchar(50)
Declare @DiscountWareNumber int

Declare @VATOfLogi Money

Declare @GlobalTransactionNumber int

Declare @RealDateTimeForTransaction DateTime

Declare @ServerTime DateTime
Declare @TransactionDatetime DateTime

Declare @Logi Money

Declare @Debit bit

Declare @ProductWareLinkNumber int

Declare @HotelCountryNumber int

Declare @DiscountAsNettoPrice bit

Select @DiscountAsNettoPrice = Convert(Bit,Keyvalue) from HotelSetup WITH (nolock) where searchkey = 'DiscountAsNettoPrice'
if isnull(@DiscountAsNettoPrice,0)=0 SET @DiscountAsNettoPrice = 0

if (@DiscountAsNettoPrice = 0) AND (@DiscountRate>0)
  SET @DiscountRate = 0

SELECT @CurrentDateStr = SYSTEMDATE FROM System WITH (NOLOCK)  WHERE NUMBER = 1  -- Only one record

SET @AChargeOnSystemDate = ISNULL(@AChargeOnSystemDate, 1)

if (@ProductNumber>0)
begin
  DECLARE @UseCashDesk BIT
  DECLARE @LastTransactionNumber INT

--  SELECT @UseCashDesk = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE Searchkey = 'FiscalPrinterSweden'
  SELECT @UseCashDesk = dbo.GetHotelSetupKeyValue ('FiscalPrinterSweden', @HotelCustomerNumber)
  SET @UseCashDesk = ISNULL(@UseCashDesk,0)

  SET @DiscountWareNumber = 0
  SET @SplitBillNumber = 0

  Select @DiscountwareText = WareName, @DiscountWareNumber = WareNumber From Ware WITH (NOLOCK) where (SortNumber=10) AND (MarkAsDeleted is null  ) AND WareGroupNumber in
  (Select WareGroupNumber From WareGroups WITH (NOLOCK) Where Payment=5 and ((MarkasDeleted='0')) OR (MarkAsDeleted is null))

  SET @AddressType=0

  Select @ResultAccountNumber = ResultAccountNumber, @BalanceAccountNumber = BalanceAccountNumber
  From Ware WITH (NOLOCK) Where WareNumber= @ProductWareNumber

  Select
    @VATValueNumber = VatValueNumber,
    @VATAccountNumber = VATAccountNumber
  From Accounts WITH (NOLOCK)
  Where Accounts.AccountNumber=@ResultAccountNumber

  exec GetVatvalueFromVATValueNumber @VATValueNumber,  @VatValueProduct OUTPUT

  Select @IsFixedPrice = FixedPrice  From Products WITH (NOLOCK)
  Where ProductNumber = @ProductNumber

  if (@PriceOverWritten=0)
  begin
    if @DateType = 0 -- arr
    begin
      SELECT
        @AdultPrice = Convert(Money,AdultPrice1),
        @Children1Price =  Convert(Money,Children1Price1),
        @AdultAddon =  Convert(Money,AdultArrivalAddon),
        @Children1Addon =  Convert(Money,Children1ArrivalAddon),
        @Children2Addon =  Convert(Money,Children2ArrivalAddon)
     From ProductPrice WITH (NOLOCK)
     Where RoomTypeNumber = @RoomTypeNumber and ProductNumber = @ProductNumber and DateStamp = Convert (Char (8), @ProductPriceDate, 112)
    end
    else if @DateType = 1 -- stay
    begin
      SELECT
        @AdultPrice =  Convert(Money,AdultPrice1),
        @Children1Price =  Convert(Money,Children1Price1),
        @AdultAddon =  Convert(Money,AdultStayAddon),
        @Children1Addon =  Convert(Money,Children1StayAddon),
        @Children2Addon =  Convert(Money,Children2StayAddon)
      From ProductPrice WITH (NOLOCK)
      Where RoomTypeNumber = @RoomTypeNumber and ProductNumber = @ProductNumber and DateStamp = Convert (Char (8), @ProductPriceDate, 112)
    end
    else
    begin
      SELECT
        @AdultPrice =  Convert(Money,AdultPrice1),
        @Children1Price =  Convert(Money,Children1Price1),
        @AdultAddon =  Convert(Money,AdultDepartureAddon),
        @Children1Addon =  Convert(Money,Children1DepartureAddon),
        @Children2Addon =  Convert(Money,Children2DepartureAddon)
      From ProductPrice WITH (NOLOCK)
      Where RoomTypeNumber = @RoomTypeNumber and ProductNumber = @ProductNumber and DateStamp = Convert (Char (8), @ProductPriceDate, 112)
    end

    if @IsFixedPrice = 0
      SET @ProductPrice = ((@AdultPrice * @Adults) + (@Children1Price * @Child1))*@Pieces
    else
    begin
      if @Child1>0
        SET @ProductPrice = (@AdultPrice + @Children1Price)*@Pieces
      else
        SET @ProductPrice = (@AdultPrice)*@Pieces
    end

    if IsNull(@AdultAddon,0)=0 SET @AdultAddon = 0
    if IsNull(@Children1Addon,0)=0 SET @Children1Addon = 0
    if IsNull(@Children2Addon,0)=0 SET @Children2Addon  = 0

    SET @AddOn = (@AdultAddon*@Adults) + (@Children1Addon*@Child1) + (@Children2Addon*@Child2)
  end
  else
    SET @ProductPrice = Round(@Price,2)

  SET @ProductPrice = ABS(@ProductPrice)
  SET @SinglePriceToPackage = Round(ABS(Convert(Money,(@ProductPrice/@Pieces))),2)

  EXEC GetNextNumber 'PRODUCTWARELINKIDENTITY', 1, @ProductWareLinkNumber OUTPUT

  SET @PackagePriceProduct = 0
  SET @VatByPacketValueProduct = 0

  if (@PackageNumber > 0)
    exec PostPackage @FolioNumber, @ReferenceNumber, @GuestNumber, @SplitBillNumber, @ResultAccountNumber, 1, 1, 1, @RoomTypeNumber, @UserIDNumber, @PackageNumber, @ProductNumber, @Pieces, @ProductPriceDate, @Adults, @Child1, @Child2, @ProductPrice, @FK_TurnoverDepartment, @DiscountRate, @PackagePriceProduct OUTPUT, @VatByPacketValueProduct OUTPUT,@ProductWareLinkNumber, @CountryNumber, @HotelCustomerNumber, @AChargeOnSystemDate, @PostAllLines = 1, @ACashDesk = @ACashDesk

  IF (@PackageNumber2 > 0)
  BEGIN
    EXEC PostPackage @FolioNumber, @ReferenceNumber, @GuestNumber, @SplitBillNumber, @ResultAccountNumber, 1, 1, 1, @RoomTypeNumber, @UserIDNumber, @PackageNumber2, @ProductNumber, @Pieces, @ProductPriceDate, @Adults, @Child1, @Child2, @ProductPrice, @FK_TurnoverDepartment, @DiscountRate, @tmpPackagePriceProduct OUTPUT, @tmpVatByPacketValueProduct OUTPUT, @ProductWareLinkNumber, @CountryNumber, @HotelCustomerNumber, @AChargeOnSystemDate, @PostAllLines = 1, @ACashDesk = @ACashDesk

    SET @PackagePriceProduct = ISNULL(@PackagePriceProduct, 0) + ISNULL(@tmpPackagePriceProduct, 0)
    SET @VatByPacketValueProduct = ISNULL(@VatByPacketValueProduct, 0) + ISNULL(@tmpVatByPacketValueProduct, 0)
  END

  IF (@PackageNumber3 > 0)
  BEGIN
    EXEC PostPackage @FolioNumber, @ReferenceNumber, @GuestNumber, @SplitBillNumber, @ResultAccountNumber, 1 , 1, 1, @RoomTypeNumber, @UserIDNumber, @PackageNumber3, @ProductNumber, @Pieces, @ProductPriceDate, @Adults, @Child1, @Child2, @ProductPrice, @FK_TurnoverDepartment, @DiscountRate, @tmpPackagePriceProduct OUTPUT, @tmpVatByPacketValueProduct OUTPUT, @ProductWareLinkNumber, @CountryNumber, @HotelCustomerNumber, @AChargeOnSystemDate, @PostAllLines = 1, @ACashDesk = @ACashDesk

    SET @PackagePriceProduct = ISNULL(@PackagePriceProduct, 0) + ISNULL(@tmpPackagePriceProduct, 0)
    SET @VatByPacketValueProduct = ISNULL(@VatByPacketValueProduct, 0) + ISNULL(@tmpVatByPacketValueProduct, 0)
  END

  if (@DayProduct=1)
  begin
    if (@IsFixedPrice=0)
      SET @Pieces = (@Adults+@Child1+@Child2)*@Pieces
    SET @SinglePriceToTrans = Round(ABS(Convert(Money,(@ProductPrice/@Pieces))),2)
  end
  else
    SET @SinglePriceToTrans = @SinglePriceToPackage

  SET @ServerTime = CURRENT_TIMESTAMP
  SET @TransactionDatetime = Convert(DateTime,@CurrentDateStr+' '+Convert(Char(8),@ServerTime,108))

  IF @AChargeOnSystemDate = 0
    SET @TransactionDatetime = CONVERT(DATETIME, CONVERT(VARCHAR(8), @ProductPriceDate, 112)+' '+CONVERT(CHAR(8), @ServerTime,108))

  if @Pieces > 0
  begin
    SET @Logi = Round(@ProductPrice - @PackagePriceProduct,2)
    SET @VATOfLogi = @Logi*(1-(1/Convert(Float,((@VatValueProduct/100)+1))))
  end
  else
  begin
    SET @Logi = Round(@ProductPrice + @PackagePriceProduct,2)
    SET @VATOfLogi = @Logi*(1-(1/Convert(Float,((@VatValueProduct/100)+1))))
  end

  SET @BillText = LTRIM(RTRIM(@BillText))

  EXEC GetNextNumber 'GlobalTransactionIdentity', 1, @GlobalTransactionNumber OUTPUT

  if (@Logi<>0 OR @VatOfLogi<>0 OR @ProductPrice>0) 
  begin
    if (@Logi - @VatOfLogi)>=0
    begin
      if @pieces>0
        SET @Debit = 0
      else
        SET @Debit = 1

      -- Krediter opholdets pris (Produktpris - Pakkepris) ps omsµtning (ophold) ps resultat excl moms
      INSERT INTO Transactions (GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite , Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, GuestNumber, OriginalDate, ExchangeRateOverwrite, ProductWareLinkNumber, HotelCustomerNumber)
      VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @TransactionDatetime, @ProductNumber, @PackageNumber, @Adults, @Child1, @Child2, @TransactionDatetime, @UserIDNumber, @FolioNumber,@Pieces, (@Logi - @VatOfLogi),@CountryNumber, 0, @Debit, 0,@ProductWareNumber,@BillText , 0, @ResultAccountNumber, @VatValueProduct, @FK_TurnoverDepartment, @Note, @GuestNumber,@TransactionDatetime , 1,@ProductWareLinkNumber, @HotelCustomerNumber)
      IF @UseCashDesk = 1
      BEGIN
        SELECT @LastTransactionNumber = @@Identity
        EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
      END
    end
    else
    begin
      if @pieces>0
        SET @Debit = 1
      else
        SET @Debit = 0
  
      INSERT INTO Transactions (GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite , Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, GuestNumber, OriginalDate, ExchangeRateOverwrite,ProductWareLinkNumber, HotelCustomerNumber)
      VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @TransactionDatetime, @ProductNumber, @PackageNumber, @Adults, @Child1, @Child2, @TransactionDatetime, @UserIDNumber, @FolioNumber,@Pieces, ABS(@Logi - @VatOfLogi),@CountryNumber, 0, @Debit, 0,@ProductWareNumber,@BillText, 0, @ResultAccountNumber, @VatValueProduct, @FK_TurnoverDepartment, @Note, @GuestNumber,@TransactionDatetime, 1,@ProductWareLinkNumber, @HotelCustomerNumber)
      IF @UseCashDesk = 1
      BEGIN
        SELECT @LastTransactionNumber = @@Identity
        EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
      END
    end

    if @pieces>0
      SET @Debit = 1
    else
      SET @Debit = 0

     -- Debiter vare incl moms (opholdets pris) ps balancekonto KUNDENS REGNING!!!
    INSERT INTO Transactions (GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, GuestNumber, OriginalDate, ExchangeRateOverwrite,ProductWareLinkNumber, HotelCustomerNumber)
    VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @TransactionDatetime, @ProductNumber, @PackageNumber, @Adults, @Child1, @Child2,@TransactionDatetime,@UserIDNumber, @FolioNumber,@Pieces,  @ProductPrice,@CountryNumber, 1, @Debit, 0,@ProductWareNumber, @BillText, 0, @BalanceAccountNumber, @VatValueProduct, @FK_TurnoverDepartment, @Note, @GuestNumber,@TransactionDatetime, 1,@ProductWareLinkNumber, @HotelCustomerNumber)
    IF @UseCashDesk = 1
    BEGIN
      SELECT @LastTransactionNumber = @@Identity
      EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
    END

    if @VatOfLogi>=0
    begin
      if @pieces>0
        SET @Debit = 0
      else
        SET @Debit = 1
 
      INSERT INTO Transactions (GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, GuestNumber, OriginalDate, ExchangeRateOverwrite,ProductWareLinkNumber, HotelCustomerNumber)
      VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @TransactionDatetime, @ProductNumber, @PackageNumber, @Adults, @Child1, @Child2,@TransactionDatetime,@UserIDNumber,@FolioNumber,@Pieces, @VatOfLogi,@CountryNumber, 0, @Debit, 0,@ProductWareNumber,@BillText,0, @VATAccountNumber, @VatValueProduct, @FK_TurnoverDepartment, @Note, @GuestNumber,@TransactionDatetime, 1,@ProductWareLinkNumber, @HotelCustomerNumber)
      IF @UseCashDesk = 1
      BEGIN
        SELECT @LastTransactionNumber = @@Identity
        EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
      END
    end
    else
    begin
      if @pieces>0
        SET @Debit = 1
      else
        SET @Debit = 0

      INSERT INTO Transactions (GlobalTransactionNumber, OriginalGlobalTransactionNumber, RealDateTimeForTransaction, ProductNumber, PackageNumber, PAXadult, PAXChild1, PAXChild2, DateStamp, UserIDNumber, FolioNumber, Pieces, PriceOverwrite, CurrencyNumber, ShowOnBillFlag, Debit, MovedDeleted, WareNumber, TextOverwrite, Payment, AccountNumber, VATValue, FK_DepartmentNumber, Note, GuestNumber, OriginalDate, ExchangeRateOverwrite,ProductWareLinkNumber, HotelCustomerNumber)
      VALUES(@GlobalTransactionNumber, @GlobalTransactionNumber, @TransactionDatetime, @ProductNumber, @PackageNumber, @Adults, @Child1, @Child2,@TransactionDatetime,@UserIDNumber,@FolioNumber,@Pieces, ABS(@VatOfLogi),@CountryNumber, 0, @Debit, 0,@ProductWareNumber,@BillText,0, @VATAccountNumber, @VatValueProduct, @FK_TurnoverDepartment, @Note, @GuestNumber,@TransactionDatetime, 1,@ProductWareLinkNumber, @HotelCustomerNumber)
      IF @UseCashDesk = 1
      BEGIN
        SELECT @LastTransactionNumber = @@Identity
        EXEC TransactionsRelationInsert @LastTransactionNumber, @ACashDesk, @UserIDNumber
      END
    end
  end
end