if (select count(*) from sys.syscolumns where tname='OtherDiskForContract' and cname='NightsOrGuests')=0
  then
    alter table OtherDiskForContract add NightsOrGuests integer default 0
end if

GO

if (select count(*) from sys.systable where table_name='NightsGuests')=0
  then
     CREATE TABLE "DBA"."NightsGuests"
       (
	"N"        		integer NOT NULL,
	"Description" 		varchar(20) NULL,
	PRIMARY KEY ("N")
        )
end if;

GO

delete from NightsGuests

GO

insert into NightsGuests(N,Description) values(0,'Нощувки');

GO

insert into NightsGuests(N,Description) values(1,'Гости');

GO

IF (EXISTS (select proc_name from sysprocedure where proc_name='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 DiskForContractNo 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
//[31.05.2005] Safa @fSrvCouns
//[04.04.2006] Jorko NightsOrGuests fro all rows