if (select count(*) from Services where ServiceNo=-20)=0 then
insert into Services(ServiceNo,ServiceName,ServicePrice,KodCurrency,Active,TipSdelka,isstandard,ServiceType,TimeSteps)
values(-20,'Отстъпка договори',0,1,0,1,1,0,0);
end if;


GO

if (select count(*) from Services where ServiceNo=-21)=0 then
insert into Services(ServiceNo,ServiceName,ServicePrice,KodCurrency,Active,TipSdelka,isstandard,ServiceType,TimeSteps)
values(-21,'Надценка договори',0,1,0,1,1,0,0);
end if;


GO


IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VaucherDisc'))) THEN
Drop procedure VaucherDisc
END IF



GO

create procedure DBA.VaucherDisc(inout @ArticleNumber integer,in @Currency integer,inout @TotalSumForVaucher1 decimal(12,2),in @ContractNo1 integer)
begin
  declare @BrojHora integer;
  declare @Broi integer;
  declare @BroiVuz integer;
  declare @BroiDeca integer;
  declare @TotalAddSum decimal(12,2);
  declare @Deposit decimal(12,2);
  declare @DiskSum decimal(12,6);
  declare @mnsign integer;
  declare @TotalNights integer;
  declare @Ads integer;
  declare @Childs integer;
  set @TotalAddSum=0;
  set @DiskSum=0;
  set @mnsign=1;
  select sum(GrandTotal) into @TotalSumForVaucher1
    from #VaucherArticles where
    RowType = 3;
  select(sum(Adult)+sum(Child)) into @BrojHora
    from #VaucherArticles;
  set @BroiVuz=0;
  set @BroiDeca=0;
  set @Broi=0;
  for fVauch as cursVauch scroll cursor for
    select distinct VaucherN as @Vauch from #VaucherArticles where VaucherN <> 0 do
    select AdultsNumber into @Ads from VtdVauchers where VaucherN = @Vauch;
    select ChildNumber into @Childs from VtdVauchers where VaucherN = @Vauch;
    set @BroiVuz=@BroiVuz+@Ads;
    set @BroiDeca=@BroiDeca+@Childs end for;
  // select sum(Adult) into @Broi from #VaucherArticles;
  // set @BroiVuz=@Broi;
  // select sum(Child) into @Broi from #VaucherArticles;
  //  set @BroiDeca=@Broi;
  for f3 as curs3 scroll cursor for
    select 
      (if sign='-' then -20 else -21 endif) as @ServNo,
      DiskName as @ArtName,
      DiskPersent as @TotDisk,
      sign as @sign,
      FreeSum as @FreeSum,
      ServiceCount as @fSrvCouns,
      ServiceCount as @SrvCouns,NightsOrGuests as @NightsOrGuests from
      OtherDiskForContract where
      ContractNo = @ContractNo1 do
    if(@TotDisk <> 0) then // ako e na procent
      set @DiskSum=(@TotalSumForVaucher1*@TotDisk)/100
    else
      set @DiskSum=@FreeSum
    end if;
    if @sign = '+' then
      set @mnsign=1
    else
      set @mnsign=-1
    end if;
    if(@TotDisk = 0) then // ako e na nostuvki, ne e na procent
      select sum(TotalNights) into @SrvCouns from #VaucherArticles where RowType = 1;
      if((@mnsign*@DiskSum*@SrvCouns) <> 0) then
        if @NightsOrGuests = 0 then
          // na nostuvki
          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,@SrvCouns*@fSrvCouns,
            @DiskSum,
            @mnsign*@DiskSum*@SrvCouns,
            @ArtName,
            0,@TotDisk,@ServNo,
            @Currency,@ArtName,null,
            null,2,0) ;
          set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*@SrvCouns)
        else // na gosti
          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,'','','','','','бр.',
            @BroiVuz,
            @BroiDeca,null,(@BroiVuz+@BroiDeca),
            @DiskSum,
            @mnsign*@DiskSum*(@BroiVuz+@BroiDeca),
            @ArtName,
            0,@TotDisk,@ServNo,
            @Currency,@ArtName,null,
            null,2,0) ;
          set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*(@BroiVuz+@BroiDeca))
        end if;
        set @ArticleNumber=@ArticleNumber+1
      end if
    else // ako e na Percent
      if @NightsOrGuests = 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,@mnsign*@SrvCouns,
          0,@mnsign*@DiskSum,@ArtName,
          0,@TotDisk,@ServNo,
          @Currency,@ArtName,null,
          null,2,0) ;
        set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*@SrvCouns)
      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) values( 
          @ArticleNumber,0,'','','','','','бр.',
          null,
          null,null,null,
          0,@mnsign*@DiskSum*(@BroiVuz+@BroiDeca),@ArtName,
          0,@TotDisk,@ServNo,
          @Currency,@ArtName,null,
          null,2,0) ;
        set @TotalAddSum=@TotalAddSum+(@mnsign*@DiskSum*(@BroiVuz+@BroiDeca))
      end if;
      set @ArticleNumber=@ArticleNumber+1
    end if end for;
  //Smqta GrandTotal na svobodnite uslugi i vmykva prazen red
  if @TotalAddSum <> 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,@TotalAddSum,null,
      null,null,null,
      null,null,null,
      null,4,0) ;
    set @ArticleNumber=@ArticleNumber+1
  end if
end



GO


IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('VtdGetSericeQtyForOtchet'))) THEN
Drop function VtdGetSericeQtyForOtchet
END IF


GO


create function DBA.VtdGetSericeQtyForOtchet(in ServiceNum integer,in FirstInvNo integer,in LastInvNo integer)
returns integer
begin
  declare qty integer;
  select sum(a.TotalNights) into qty
    from VaucherArticles as a,VaucherInvoices as i where
    (a.ServiceNo = ServiceNum) and
    (a.Invoiceno >= FirstInvNo and a.InvoiceNo <= LastInvNo) and
    i.CancelInvoiceNo = 0 and
    a.InvoiceNo = i.VaucherInvoiceNo;
  if qty is null then
    return(0)
  else
    return(qty)
  end if
end