-- Скрипт 0593
-- 1. fix на допълнителни отстъпки/надбавки за ваучер

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

Go

create procedure 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
-- ---------------------------------
  Message '+ VaucherDiscNadcenka START';
  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
    from
      OtherDiskForVaucher
    where
      ContractNo = @ContractNo1
-- Yanko +, razbivane po gost
      and ((DiskType in (0,1) and @GuestAgeGroup >= 0) or (DiskType not in (0,1) and @GuestAgeGroup < 0))
    order by DiskType desc
-- Yanko -
    do
      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;
          set @GrantTotal=@SumWithoutVat;
        else
	      set @Discount=@FreeSum;	
		  set @GrantTotal = @TotalSumForVaucher1 * (@Discount/100);
        end if;
      -- za noshtuvki
      if @DiskType=0 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,@FreeSum,@ServNo,
            @Currency,@ArtName,null,
            null,10,@VaucherN1,@TmpGuestID) ;
            set @ArticleNumber=@ArticleNumber+1;
      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=1 and @GuestAgeGroup=1)) 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;
      -- za vaucheri
      else
      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,10,@VaucherN1,0) ;
            set @ArticleNumber=@ArticleNumber+1;
      end if end if end if;
  end for;
  Message '+ VaucherDiscNadcenka END';
end;
//[v.1 13.Jul.2007] - Yanko, created, razdeliane po gosti
//[v.2 08.Nov.2007] - Yanko, fix otstypki za celia vaucher

Go

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

GO

create procedure
DBA.PrepareVauchersBill(in @ContractNo integer,in @place varchar(50),in @VatType integer)
// TaziF procedura se izpolzva za napylwane na wremennata tablica #VaucherArticles
// S uslugite koito triabva da plashtat gostite nastaneni po vaucheri ot daden dogovor
begin
  declare @Currency integer;
  declare @ArticleNumber integer;
  //  declare @FreeDays integer;
  declare @People integer;
  //  declare @Deposit decimal(12,2);
  declare @Adult integer;
  declare @Child integer;
  declare @exCount integer;
  declare @exType integer;
  declare @PayedLodgings integer;
  declare @valPrice decimal(12,2);
  declare @Disc decimal(12,2);
  declare @TotalSum double;
  declare @ActiveSeasson integer;
  declare @IsForAlServices integer;
  declare @dobavka integer;
  declare @MaxPrice decimal(12,2);
  declare @MinPrice decimal(12,2);
  declare @TotalSumForVaucher double;
  declare @ConNo integer;
  declare @VVVAucherNum varchar(30);
  declare @FlagWithSubTotals integer;
  declare @BroiSubTotals integer;
  declare @DateArrive date;
  declare @LArticleNumber integer; // ivaylo
  declare @MinAn integer;
  declare @TotalSumForGuest decimal(12,2); // Yanko
  declare @GuestArticleNo integer; // Yanko
  declare @TempGuestID integer; // Yanko
  declare @HasGuestExtres integer; // Yanko
  -- ----------------------------------------------------------------------
  -- za redowete v #VaucherArticles pri RowType =
  --  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 Grand Total  = suma na Vsichko
  --  7 depozit
  -- 10 - Otstapka/nadbavka po vaucher/gost
  -- 11 - SubTotal za gost
  -- 12 - Bezplatni noshtuvki po gost
  -- ----------------------------------------------------------------------
  message '+ PrepareVauchersBill START' type info to console;
  delete from #VaucherArticles;
  set bvrBroi=0;
  set @People=0;
  set @Adult=0;
  set @Child=0;
  set @TotalSum=0;
  set @dobavka=0;
  set @MaxPrice=0;
  set @MinPrice=0;
  set @ActiveSeasson=0;
  set @TotalSumForVaucher=0;
  set @BroiSubTotals=0;
  select IntStojnost into @FlagWithSubTotals
    from Registers where RegisterName = 'VSmetkaSubTotals';
  set @ArticleNumber=1;
  set @HasGuestExtres= if (select count(*) from OtherDiskForVaucher where ContractNo = @ContractNo) > 0 then 1 else 0 endif;
  -- Proverqva dali ima ostanovena otstypka za vsi4ki uslugi ot paketa uslugi po dogovor
  select C.Discount into @Disc from Contracts as C where C.ContractNo = @ContractNo;
  if(@Disc is null) then set @Disc=0
  end if;
  select ExtresCount,ExtresType into @exCount,@exType from Contracts as c where c.ContractNo = @ContractNo;
  for f1 as curs1 scroll cursor for
    select v.VaucherNo as @VaucherNo,v.VaucherNum as @VaucherNum,s.ServiceName as @ArticleName,
      v.ContractNo,v.VaucherBoardPlan as @BoardPlanNo,v.NumAdults as @NumAdult,v.NumChild as @Numchild,
      v.NumLodgings as @ServiceCount,v.ServiceNo as @ServiceNum from
      VauchersForInvoice as v left outer join
      Services as s on
      (v.ServiceNo = s.ServiceNo) where
      v.ContractNo = @ContractNo do
    if @HasGuestExtres = 1 then
      -- ако има отсъпки/надценки по ваучер
      for f2 as curs2 scroll cursor for
        select AdultType as @AdultType,HumanKind as @HumanKind,RoomType as @RoomType,
          HasExtraBed as @HasExtraBed,Name as @GuestName from
          VaucherGuests where
          VaulcherNo = @VaucherNo order by
          AdultType asc,HasExtraBed asc do
        select DateArrive into @DateArrive from VtdVauchers where
          VaucherN = @VaucherNo and
          VaucherNum = @VaucherNum;
        set @GuestArticleNo=@ArticleNumber;
        set @TempGuestID=IsNull((select Max(TempGuestID)+1 from #VaucherArticles),1);
        -- dobaviane red za bezplatni noshtuvki, zadadeni v dogovora
        call VaucherExtresType(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo,
          @NumAdult,@Numchild,@ServiceCount,@ServiceNum,1,@ArticleName,@GuestName,@Disc,
          @HumanKind,@AdultType,@HasExtraBed,@RoomType, @ContractNo,@DateArrive,days(@DateArrive,@ServiceCount),
          @ServiceCount,@VatType,@TempGuestID);
        set @TotalSumForGuest=(select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID);
        -- dobaviane red za bezpl noshtuvki, zadadeni vav formata za otstapki/nadcenki za vaucher, kato dvata vida
        -- bezplatni noshtuvki se razlichavat kato logika - cenata na noshtuvkata na vtorite e namalena s otstykata
        call VaucherGuestExtres(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo,
          @NumAdult,@Numchild,@ServiceCount,@ServiceNum,1,@ArticleName,@GuestName,@Disc,
          @HumanKind,@AdultType,@HasExtraBed,@RoomType,@ContractNo,@DateArrive,
          days(@DateArrive,@ServiceCount),@ServiceCount,@VatType,@TempGuestID);
        set @Currency=IsNull((select Max(KodCurrency) from ContractsPriceList where ContractNo = @ContractNo),1);
        -- dobaviane na drugi otstypki ili nadcenki po vaucher/gost
        call VaucherDiscNadcenka(@ArticleNumber,@Currency,@TotalSumForGuest,@ContractNo,@VaucherNo,@VatType,@AdultType,
          @TempGuestID);
        set @TotalSumForGuest=(select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID);
        set @ArticleNumber=@ArticleNumber+1;
        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,@VaucherNo,null,null,null,
          null,null,'бр.',null,null,
          null,null,null,@TotalSumForGuest,null,
          null,null,null,null,null,
          null,null,11,0,@TempGuestID) ;
        set @ArticleNumber=@ArticleNumber+1 end for
    else
      -- няма отстъпки/надценки, разбити по ваучер и гост
      for f222 as curs222 scroll cursor for
        select AdultType as @AdultType,HumanKind as @HumanKind,RoomType as @RoomType,
          HasExtraBed as @HasExtraBed,min(Name) as @GuestName,count(*) as @NumPeople from
          VaucherGuests where
          VaulcherNo = @VaucherNo
          group by AdultType,HumanKind,RoomType,HasExtraBed order by
          AdultType asc,HasExtraBed asc do
        select DateArrive into @DateArrive from VtdVauchers where
          VaucherN = @VaucherNo and
          VaucherNum = @VaucherNum;
        set @GuestArticleNo=@ArticleNumber;
        set @TempGuestID=IsNull((select Max(TempGuestID)+1 from #VaucherArticles),1);
        call VaucherExtresType(@ArticleNumber,@VaucherNo,cast(@VaucherNum as varchar(30)),@BoardPlanNo,
          @NumAdult,@Numchild,@ServiceCount,@ServiceNum,@NumPeople,@ArticleName,@GuestName,@Disc,
          @HumanKind,@AdultType,@HasExtraBed,@RoomType,@ContractNo,@DateArrive,days(@DateArrive,@ServiceCount),
          @ServiceCount,@VatType,@TempGuestID);
        set @Currency=IsNull((select Max(KodCurrency) from ContractsPriceList where ContractNo = @ContractNo),1);
        set @TotalSumForGuest=(select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where TempGuestID = @TempGuestID);
        set @ArticleNumber=@ArticleNumber+1;
        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,@VaucherNo,null,null,null,
          null,null,'бр.',null,null,
          null,null,null,@TotalSumForGuest,null,
          null,null,null,null,null,
          null,null,11,0,@TempGuestID) ;
        set @ArticleNumber=@ArticleNumber+1 end for
    end if;
    set @VVVAucherNum=cast(@VaucherNum as varchar(30));
    //------------------------------- ivaylo
    -- Yanko +, razbivane po gost
    set @TotalSumForVaucher=IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VaucherNo),0);
    -- ако нямаме разбиване по гост, трием тотал за гост
    if @HasGuestExtres = 0 then delete from #VaucherArticles where RowType = 11
    end if; -- Yanko -
    call VaucherDiscNadcenka(@ArticleNumber,@Currency,@TotalSumForVaucher,@ContractNo,@VaucherNo,@VatType,-1,0);
    //------------------------------- ivaylo
    -- Yanko +, razbivane po gost;  za da ne se smiata v totalite nadolu
    update #VaucherArticles set VaucherN = null where RowType = 11;
    -- Yanko -
    set @TotalSumForVaucher=IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where ForVaucherN = @VaucherNo),0);
    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) values(
      @ArticleNumber,0,null,null,null,
      null,null,'бр.',null,null,
      null,null,null,@TotalSumForVaucher,null,
      null,null,null,null,null,
      null,null,3,@VaucherNo) ;
    set @ArticleNumber=@ArticleNumber+1 end for;
    -- presmiatane Total na vauchera, s vsichki noshtuvki, uslugi i otstypki/nadcenki za vsuchki gosti po nego
    set @TotalSum=IsNull((select Sum(IsNull(GrandTotal,0)) from #VaucherArticles where RowType = 3),0);
    if(@TotalSum <> 0) 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) values(
        @ArticleNumber,0,null,null,null,
        null,null,'бр.',null,null,
        null,null,null,@TotalSum,null,
        null,null,null,null,'       ',
        null,null,5,0) ;
      set @ArticleNumber=@ArticleNumber+1;
      set @ADult=0;
      set @Child=0
    end if;
    -- Vzema i smetkosva svobodnite uslugi koito sa % ili suma
  call VaucherDisc(@ArticleNumber,@Currency,@TotalSum,@ContractNo,@VatType);
  set @ArticleNumber=@ArticleNumber+1;
  select sum(IsNull(GrandTotal,0)) into @TotalSum from #VaucherArticles where RowType in(4,5);
  -- presmiatane na Grand Total
  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) values(
    @ArticleNumber,0,null,null,null,
    null,null,'бр.',null,null,
    null,null,null,@TotalSum,null,
    null,null,null,null,'       ',
    null,null,6,0) ;
  -- ako v registrite e ukazano smetka bez subtotali, triem subtotalite
  if @FlagWithSubTotals = 0 then
    delete from #VaucherArticles where RowType = 3;
    delete from #VaucherArticles where
      RowType = 0 and
      ArticleNumber >= 1 and
      ArticleNumber <= (select max(ArticleNumber) from #VaucherArticles where RowType = 1)
  end if;
  -- iztrivane total na vaucher, ako v invoisa ima samo edin vaucher
  if(select count(*) from #VaucherArticles where RowType = 3) = 1 then
    delete from #VaucherArticles where RowType = 3
  end if;
  -- sortirane na zapisite
  //update #VaucherArticles set ArticleNumber = Number(); // Yanko
  while((select Max(ArticleNumber) from #VaucherArticles) > 0) loop
    set @MinAn=(select Min(ArticleNumber) from #VaucherArticles);
    if @MinAn > 0 then set @MinAn=0
    end if;
    update #VaucherArticles set ArticleNumber = @MinAn-1 where ArticleNumber =
      (select Min(ArticleNumber) from #VaucherArticles where ArticleNumber > 0)
  end loop;
  update #VaucherArticles set ArticleNumber = ArticleNumber*(-1);
  message '+ PrepareVauchersBill END' type info to console
end
//[v.1 xx.Jul.2007] - Ivaylo
//[v.2 13.Jul.2007] - Yanko, razdeliane po gosti
//[v.3 08.Nov.2007] - Yanko, fix otstypki za celia vaucher