IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('VaucherDiscNadcenka')))
THEN Drop procedure VaucherDiscNadcenka
END IF

GO
create procedure
DBA.VaucherDiscNadcenka(inout @ArticleNumber integer,in @Currency integer,in @TotalSumForVaucher1 double,in @ContractNo1 integer,in @VaucherN1 integer,in @VatType integer,in @GuestAgeGroup integer,in @TmpGuestID integer)
begin
  -- dobavia otstypki/nadcenki, zadadeni v +- za vaucher, bez bezplatnite noshtuvki, kym #VaucherArticles
  -- procedurata se vika poveche ot 1 pyt - za gost i za vaucher, kato
  -- ako GuestAgeGroup e >= 0, se zapisvat samo otstapki/nadbavki za gost, inache (-1) vsichki ostanali
  -- ( otstypki i nadbavki pri razbivane po vaucher/gost imat RowType = 10 ! )
  declare @mnsign integer;
  declare @Ads integer;
  declare @Childs integer;
  declare @ObshtoHora integer;
  declare @Lodgings integer;
  declare @SumWithoutVat double;
  declare @Discount double;
  declare @GrantTotal double;
  declare @VatRate double; // Yanko
  --Maria +
  declare @TotalNights integer;
  declare @TotalSumForVaucherNew double;
  --Maria -
  -- ---------------------------------
  --  Poleto RowType  opredelia kakuv tip e reda
  --  1 Red za Vaucherna usluga za daden gost
  --  2 Red za Dopulnitelna usluga za daden gost
  --  3 SubTotal na Vaucher - suma na nostuvkite
  --  4 SubTotal na Extreses - suma na dopulnitelnite uslugi
  --  5 Total Vaucher = suma na nostuvkite + suma na dopulnitelnite uslugi
  --  6 Super Total  = suma na Vsichko
  --  7 depozit
  -- 10 - Otstapka/nadbavka po gost
  -- 11 - SubTotal za gost
  -- 12 - Otstapka/nadbavka po vaucher
  -- 13 - Bezplatni noshtuvki po gost
  -- ---------------------------------
  message '+ VaucherDiscNadcenka START' type info to console;
  set @VatRate=100*(select Rate from VATRates where ID = 4);
  select AdultsNumber,ChildNumber,ValNumLodgings into @Ads,@Childs,
    @Lodgings from VtdVauchers where
    VaucherN = @VaucherN1;
  for f3 as curs3 scroll cursor for
    select(if sign = '-' then-20 else-21 endif) as @ServNo,
      DiskName as @ArtName,
      sign as @sign,
      FreeSum as @FreeSum,
      PersentOrSum as @PersentOrSum,
      DiskType as @DiskType,
      AdultType as @AdultType,
      --Maria +
      OrderNumber as @OrderNumber,
      Zakraglenie as @Zakraglenie from
      --Maria -
      OtherDiskForVaucher where
      ContractNo = @ContractNo1 and
      (AdultType = @GuestAgeGroup or AdultType <0) and
      -- Yanko +, razbivane po gost
      ((DiskType in( 0,1) and @GuestAgeGroup >= 0) or(DiskType not in( 0,1) and @GuestAgeGroup < 0)) order by
      DiskType ,
      --Maria +
      OrderNumber asc do
      --Maria -
    -- Yanko -
    --Maria +
    if @OrderNumber = 0 then
      --Maria -
      if @sign = '+' then
        set @mnsign=1
      else
        set @mnsign=-1
      end if;
      if @VatType = 1 then
        set @SumWithoutVat=(@FreeSum-(@FreeSum*@VatRate)/(100+@VatRate))
      else
        set @SumWithoutVat=@FreeSum
      end if;
      set @TotalSumForVaucher1=IsNull(@TotalSumForVaucher1,0);
      if @PersentOrSum = 0 then
        set @Discount=0;
       --Maria +
        if @Zakraglenie='0' then
         set @GrantTotal="truncate"(@SumWithoutVat,2)
        else
       --Maria -
         set @GrantTotal=@SumWithoutVat
       --Maria +
       end if
       --Maria -
      else
        set @Discount=@FreeSum;
        --Maria +
        if @Zakraglenie='0' then
          set @GrantTotal="Truncate"(@TotalSumForVaucher1*(@Discount/100),2)
        else
        --Maria -
          set @GrantTotal=@TotalSumForVaucher1*(@Discount/100)
        --Maria +
        end if
        --Maria -
      end if;
      -- za noshtuvki
      if @DiskType = 0 then
        --Maria +
        if @PersentOrSum = 0 then
          set @TotalNights=(select TotalNights from #VaucherArticles where RowType = 1 and TempGuestID = @TmpGuestID);
          --Maria -
          insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
            VaucherSeason,VaucherBoard,VaucherRoomType,
            VaucherBedType,Measure,Adult,
            Child,Nights,TotalNights,
            SinglePrice,
            GrandTotal,
            ArticleName,
            CreditNo,Discount,ServiceNo,
            Currency,TranslateName,TranslateSeason,
            TranslateBed,RowType,ForVaucherN,TempGuestID) values(
            @ArticleNumber,0,'','','','','','απ.',
            null,
            --Maria +
            //null,null,null,
            null,null,@TotalNights,
            --Maria -
            --Maria +
            //0,
            @GrantTotal,
            --Maria -
            @mnsign*@GrantTotal*@TotalNights,
            @ArtName,
            --Maria +
            //0,@FreeSum,@ServNo,
            0,@Discount,@ServNo,
            --Maria -
            @Currency,@ArtName,null,
            null,10,@VaucherN1,@TmpGuestID) ;
          set @ArticleNumber=@ArticleNumber+1
        else --Maria +
          insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
            VaucherSeason,VaucherBoard,VaucherRoomType,
            VaucherBedType,Measure,Adult,
            Child,Nights,TotalNights,
            SinglePrice,
            GrandTotal,
            ArticleName,
            CreditNo,Discount,ServiceNo,
            Currency,TranslateName,TranslateSeason,
            TranslateBed,RowType,ForVaucherN,TempGuestID) values(
            @ArticleNumber,0,'','','','','','απ.',
            null,
            null,null,null,
            0,
            @mnsign*@GrantTotal,
            @ArtName,
            0,@FreeSum,@ServNo,
            @Currency,@ArtName,null,
            null,10,@VaucherN1,@TmpGuestID) ;
          set @ArticleNumber=@ArticleNumber+1
        end if
      else
        -- za gosti
        if @DiskType = 1 then
          -- Yanko +, razbivane po gost
          if((@Ads > 0 and @AdultType = 0 and @GuestAgeGroup = 0) or(@Childs > 0 and @AdultType > 0 and @GuestAgeGroup > 0)) then
            set @ObshtoHora=1;
            -- Yank0 -
            insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
              VaucherSeason,VaucherBoard,VaucherRoomType,
              VaucherBedType,Measure,Adult,
              Child,Nights,TotalNights,
              SinglePrice,
              GrandTotal,
              ArticleName,
              CreditNo,Discount,ServiceNo,
              Currency,TranslateName,TranslateSeason,
              TranslateBed,RowType,ForVaucherN,TempGuestID) values(
              @ArticleNumber,0,'','','','','','απ.',
              null,
              null,null,@ObshtoHora,
              @GrantTotal,
              @mnsign*@GrantTotal,
              @ArtName,
              0,@Discount,@ServNo,
              @Currency,@ArtName,null,
              null,10,@VaucherN1,@TmpGuestID) ;
            set @ArticleNumber=@ArticleNumber+1
          end if
        else -- za vaucheri
          if @DiskType = 2 then
            insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
              VaucherSeason,VaucherBoard,VaucherRoomType,
              VaucherBedType,Measure,Adult,
              Child,Nights,TotalNights,
              SinglePrice,
              GrandTotal,
              ArticleName,
              CreditNo,Discount,ServiceNo,
              Currency,TranslateName,TranslateSeason,
              TranslateBed,RowType,ForVaucherN,TempGuestID) values(
              @ArticleNumber,0,'','','','','','απ.',
              null,
              null,null,null,
              0,
              @mnsign*@GrantTotal,
              @ArtName,
              0,@Discount,@ServNo,
              @Currency,@ArtName,null,
              null,12,@VaucherN1,0) ;
            set @ArticleNumber=@ArticleNumber+1
          end if
        end if
      end if
    end if; --Maria +
    if @OrderNumber >= 1 then
      if @sign = '+' then
        set @mnsign=1
      else
        set @mnsign=-1
      end if;
      if @VatType = 1 then
        set @SumWithoutVat=(@FreeSum-(@FreeSum*@VatRate)/(100+@VatRate))
      else
        set @SumWithoutVat=@FreeSum
      end if;
      set @TotalSumForVaucher1=IsNull(@TotalSumForVaucher1,0);
      set @TotalSumForVaucherNew=IsNull(@TotalSumForVaucherNew,0);
      if @PersentOrSum = 0 then
        set @TotalNights=(select TotalNights from #VaucherArticles where RowType = 1 and TempGuestID = @TmpGuestID);
        set @Discount=0;
        if @Zakraglenie = '0' then
          set @GrantTotal="truncate"(@SumWithoutVat,2)
        else
          set @GrantTotal=@SumWithoutVat
        end if;
        if @DiskType = 0 then
          set @TotalSumForVaucherNew=@TotalSumForVaucher1+@mnsign*@GrantTotal*@TotalNights;
        else set @TotalSumForVaucherNew=@TotalSumForVaucher1+@mnsign*@GrantTotal
        end if
      else
        if @DiskType <> 2 then
          if @OrderNumber = (select min(OrderNumber) from OtherDiskForVaucher where OrderNumber>0 and DiskType<>2 and ContractNo=@ContractNo1) then
            set @Discount=@FreeSum;
            if @Zakraglenie = '0' then
               set @GrantTotal="Truncate"(@TotalSumForVaucher1*(@Discount/100),2)
            else
              set @GrantTotal=@TotalSumForVaucher1*(@Discount/100)
            end if;
            set @TotalSumForVaucherNew=@TotalSumForVaucher1+@mnsign*@GrantTotal
          end if;
          if @OrderNumber > (select min(OrderNumber) from OtherDiskForVaucher where OrderNumber>0 and DiskType<>2 and ContractNo=@ContractNo1) then
            set @Discount=@FreeSum;
            if @Zakraglenie='0' then
              set @GrantTotal="truncate"(@TotalSumForVaucherNew*(@Discount/100),2)
            else
              set @GrantTotal=@TotalSumForVaucherNew*(@Discount/100)
            end if;
            set @TotalSumForVaucherNew=@TotalSumForVaucherNew+@mnsign*@GrantTotal
          end if
        end if;
        if @DiskType = 2 then
          if @OrderNumber = (select min(OrderNumber) from OtherDiskForVaucher where OrderNumber>0 and DiskType=2 and ContractNo=@ContractNo1) then
            set @Discount=@FreeSum;
            if @Zakraglenie = '0' then
               set @GrantTotal="Truncate"(@TotalSumForVaucher1*(@Discount/100),2)
            else
              set @GrantTotal=@TotalSumForVaucher1*(@Discount/100)
            end if;
            set @TotalSumForVaucherNew=@TotalSumForVaucher1+@mnsign*@GrantTotal
          end if;
          if @OrderNumber > (select min(OrderNumber) from OtherDiskForVaucher where OrderNumber>0 and DiskType=2 and ContractNo=@ContractNo1) then
            set @Discount=@FreeSum;
            if @Zakraglenie='0' then
              set @GrantTotal="truncate"(@TotalSumForVaucherNew*(@Discount/100),2)
            else
              set @GrantTotal=@TotalSumForVaucherNew*(@Discount/100)
            end if;
            set @TotalSumForVaucherNew=@TotalSumForVaucherNew+@mnsign*@GrantTotal
          end if
        end if;
      end if;
      -- za noshtuvki
      if @DiskType = 0 then
        if @PersentOrSum = 0 then
          set @TotalNights=(select TotalNights from #VaucherArticles where RowType = 1 and TempGuestID = @TmpGuestID);
          insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
            VaucherSeason,VaucherBoard,VaucherRoomType,
            VaucherBedType,Measure,Adult,
            Child,Nights,TotalNights,
            SinglePrice,
            GrandTotal,
            ArticleName,
            CreditNo,Discount,ServiceNo,
            Currency,TranslateName,TranslateSeason,
            TranslateBed,RowType,ForVaucherN,TempGuestID) values( 
            @ArticleNumber,0,'','','','','','απ.',
            null,
            null,null,@TotalNights,
            @GrantTotal,
            @mnsign*@GrantTotal*@TotalNights,
            @ArtName,
            0,@Discount,@ServNo,
            @Currency,@ArtName,null,
            null,10,@VaucherN1,@TmpGuestID) ;
          set @ArticleNumber=@ArticleNumber+1
        else
          insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
            VaucherSeason,VaucherBoard,VaucherRoomType,
            VaucherBedType,Measure,Adult,
            Child,Nights,TotalNights,
            SinglePrice,
            GrandTotal,
            ArticleName,
            CreditNo,Discount,ServiceNo,
            Currency,TranslateName,TranslateSeason,
            TranslateBed,RowType,ForVaucherN,TempGuestID) values( 
            @ArticleNumber,0,'','','','','','απ.',
            null,
            null,null,null,
            0,
            @mnsign*@GrantTotal,
            @ArtName,
            0,@FreeSum,@ServNo,
            @Currency,@ArtName,null,
            null,10,@VaucherN1,@TmpGuestID) ;
          set @ArticleNumber=@ArticleNumber+1
        end if
      else
        -- za gosti
        if @DiskType = 1 then
          -- Yanko +, razbivane po gost
          if((@Ads > 0 and @AdultType = 0 and @GuestAgeGroup = 0) or(@Childs > 0 and @AdultType > 0 and @GuestAgeGroup > 0)) then
            set @ObshtoHora=1;
            -- Yank0 -
            insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
              VaucherSeason,VaucherBoard,VaucherRoomType,
              VaucherBedType,Measure,Adult,
              Child,Nights,TotalNights,
              SinglePrice,
              GrandTotal,
              ArticleName,
              CreditNo,Discount,ServiceNo,
              Currency,TranslateName,TranslateSeason,
              TranslateBed,RowType,ForVaucherN,TempGuestID) values( 
              @ArticleNumber,0,'','','','','','απ.',
              null,
              null,null,@ObshtoHora,
              @GrantTotal,
              @mnsign*@GrantTotal,
              @ArtName,
              0,@Discount,@ServNo,
              @Currency,@ArtName,null,
              null,10,@VaucherN1,@TmpGuestID) ;
            set @ArticleNumber=@ArticleNumber+1
          end if
        else -- za vaucheri
          if @DiskType = 2 then
            insert into #VaucherArticles( ArticleNumber,VaucherN,VaucherNum,
              VaucherSeason,VaucherBoard,VaucherRoomType,
              VaucherBedType,Measure,Adult,
              Child,Nights,TotalNights,
              SinglePrice,
              GrandTotal,
              ArticleName,
              CreditNo,Discount,ServiceNo,
              Currency,TranslateName,TranslateSeason,
              TranslateBed,RowType,ForVaucherN,TempGuestID) values( 
              @ArticleNumber,0,'','','','','','απ.',
              null,
              null,null,null,
              0,
              @mnsign*@GrantTotal,
              @ArtName,
              0,@Discount,@ServNo,
              @Currency,@ArtName,null,
              null,12,@VaucherN1,0) ;
            set @ArticleNumber=@ArticleNumber+1
          end if
        end if
      end if
    end if;
    set @TotalSumForVaucherNew=IsNull(@TotalSumForVaucherNew,0) end for;
  --Maria -
  message '+ VaucherDiscNadcenka END' type info to console
end
//[v.1 13.Jul.2007] - Yanko, created, razdeliane po gosti
//[v.2 08.Nov.2007] - Yanko, fix otstypki za celia vaucher
//[v.3 08.Jul.2008] - Kozludjov, otstypki s natrupvane