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


GO


create function
DBA.FinishVaucherProformBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @TotalNoVAT double,in @VAT double,in @GrandTotal double,in @WithoutVAT double,in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate double,in @ContractNo integer)
returns integer
//Insertva dannite fakturata vuv Proform i ProVaucherArticles i sled tova po tazi Proforma moje da se izdava faktura za smetka dogovor - vaucher
begin atomic
  declare @ProformNo integer;
  declare @DepositNo integer;
  declare @TrueInvoiceNo integer;
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  //declare @CreditNo integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @ArtNo integer;
  //Vzima Tipa valuta ot vida smetka
  select KodCurrency into @SmetkaCurrency from VidSmetki where N = @VidSmetka;
  //Iztriva nulevite kolichestva
  delete from #VaucherArticles where TotalNights = 0;
  //Vmukva dannite za fakturata
  set @ProformNo=GetCounter('TrueProfNo');
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  insert into Proform( ProformNo,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo) values( 
    @ProformNo,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@TrueInvoiceNo,@WithoutVAT,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo) ;
  set @ArtNo=1;
  //  return;
  for f1 as curs1 scroll cursor for
    select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,
      SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan,RoomType as @RoomType from
      #VaucherArticles where
      RowType = 1 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
  for f2 as curs2 scroll cursor for
    select ArticleNumber,VaucherN as @VaucherNo,VaucherNum as @VaucherNum,VaucherSeason as @Seasson,
      VaucherBoard as @BoardPlan,VaucherRoomType as @Room,VaucherBedType as @AdditionBed,Adult as @Adult,Child as @Child,
      Nights as @Nights,TotalNights as @TotalNights,SinglePrice as @RealPrice,GrandTotal as @GrTotal,ArticleName as @ArticleName,
      Discount as @Disc,ServiceNo as @ServiceNum,Currency as @Currency,CreditNo as @CreditNo,
      TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,TranslateBed as @TranslateBed,
      GuestName as @GuestName,TranslateGuestName as @TranslateGuestName,
      SeasonNo as @SeassonNo,HumanKind as @HumanKind,AdultType as @AdultType,ExtraBed as @HasExtraBed,BoardPlan as @ValBoardPlan,RoomType as @RoomType from
      #VaucherArticles where
      RowType = 2 order by
      ArticleNumber asc do
    insert into ProVaucherArticles( ProformNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,GuestName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,TranslateGuestName,
      SeasonNo,HumanKind,AdultType,ExtraBed,BoardPlan,RoomType) values( 
      @ProformNo,@ArtNo,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@Currency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType) ;
    set @ArtNo=@ArtNo+1 end for;
  //set @TotalSum=@TotalSum+(@OkCancel*@GrTotal);
  //set @ArtNumber=@ArtNumber+1
  /*
  for f as curs scroll cursor for
  select ArticleNumber as @ArticleNumber,ArticleName as @ArticleName,ArticleQty as @ArticleQty,
  ArticlePrice as @ArticlePrice,RegNo as @RegNo,CreditNo as @CreditNum,ArticleSum as @ArticleSum,
  TipDDS as @TipDDS,Discount as @Discount,ServiceNo as @Service,ArticleSumInvCurrency as @SumInvCurrency from #Articles do
  //Insertva zapis vuv Articles
  insert into ProArticles(ProformNo,RegNo,Price,CreditNo,TotalSum,TotalSumInvCurrency,Qty,KodCurrency,ArticleNo,
  ArticleName,TipDDS,Discount,ServiceNo) values(@ProformNo,@RegNo,@ArticlePrice,@CreditNum,@ArticleSum,@SumInvCurrency,
  @ArticleQty,1,@ArticleNumber,@ArticleName,@TipDDS,@Discount,@Service) end for;
  */
  return(@ProformNo)
end