if (select count(*) from syscolumn where column_name='MaskaFaktura' and
    table_id=(select table_id from systable where table_name='VaucherInvoices')) =0 then
    
    
Alter table VaucherInvoices
Add MaskaFaktura integer NULL;

end if


GO



if (select count(*) from syscolumn where column_name='InvoiceNo' and
    table_id=(select table_id from systable where table_name='VtdVauchers')) =0 then

Alter table VtdVauchers
Add InvoiceNo  integer default 0;

end if;

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

GO

create function
DBA.FinishVaucherBill(in @PayHow integer,in @InvoiceType integer,in @InvoiceDate integer,in @KodAdmin integer,in @Whom varchar(50),in @GrandTotal decimal(12,2),in @BillType integer,in @No integer,in @IsInvoice integer,in @VidSmetka integer,in @ExchangeRate decimal(12,4),in @ContractNo integer)
returns integer
//Insertva dannite za fakturata vuv Invoices i Articles i insertva parite v ResMoney
//@No e syotvetno nomer na grupa, rezervaciq, gost, staia i anulirana smetka, ako smetkata e za grupa, rezervacia, gost, staia ili anulirashta
begin atomic
  declare @InvoiceNo integer;
  declare @DepositNo integer;
  declare @TrueInvoiceNo integer;
  declare @CancelInvoiceNo integer;
  declare @ServiceNo integer;
  declare @CreditNo integer;
  declare @ArtNum integer;
  declare @ResMoneyStatus integer;
  declare @SmetkaCurrency integer;
  declare @NullDepositCount integer;
  declare @TotalNoVAT    decimal(12,2); //ivaylo
  declare @VAT           decimal(12,2); //ivaylo
  declare @InvoiceMask integer; //ivaylo
//------------------------------------------------------------------- ivaylo
    set @TotalNoVAT=@GrandTotal*1.07;
    set @VAT=(@TotalNoVAT*7)/107;
    message '---------------------------   @GrandTotal='||@GrandTotal||'    @TotalNoVAT='||@TotalNoVAT;
//------------------------------------------------------------------- ivaylo
  if @BillType <> 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1005
  end if;
  if @BillType = 7 then
    select Count(ServiceNo) into @NullDepositCount from #Articles where ArticleQty = 0 and ServiceNo = 1004
  end if;
  //message cast(@NullDepositCount as varchar);
  //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 @InvoiceNo=GetCounter('InvoiceCounter'); //    //ivaylo
  message '@InvoiceNo -> '+cast(@InvoiceNo as varchar) type info to console;
  if @IsInvoice = 1 then
    set @TrueInvoiceNo=GetCounter('TrueInvoiceNo') //TrueVaucherInvoiceNo ivaylo
  else
    set @TrueInvoiceNo=0
  end if;
  //message cast(@TrueInvoiceNo as varchar);
  if @BillType = -1 then
    set @CancelInvoiceNo=@No
  else
    set @CancelInvoiceNo=0
  end if;
  set @ResMoneyStatus=1;
  set @ArtNum=1;
  select IntStojnost into @InvoiceMask from Registers where RegisterName = 'MaskaFaktura'; //ivaylo
  //Trqbva da se dobavi i nomera na dogovora pri insert-a na nova smetka
  insert into VaucherInvoices( VaucherInvoiceNo,Status,PayHow,InvoiceType,InvoiceDate,BankTown,Upolnomosten,
    UpolnomDate,UpolnomNum,KodAdmin,Whom,MOL,WhomTown,WhomDanNum,Bulstat,KodCurrency,
    TotalNoVAT,VAT,GrandTotal,CancelInvoiceNo,TrueInvoiceNo,WithoutVAT,TrueInvoiceDate,ExchangeRate,VidSmetka,ContractNo,MaskaFaktura) values(
    @InvoiceNo,1,@PayHow,@InvoiceType,@InvoiceDate,'','','','',@KodAdmin,
    @Whom,'','','','',@SmetkaCurrency,@TotalNoVAT,@VAT,@GrandTotal,@CancelInvoiceNo,@TrueInvoiceNo,0,FloatToDate(@InvoiceDate),@ExchangeRate,@VidSmetka,@ContractNo,@InvoiceMask) ;
  //Insertva kato zapis sumata ot vsi4ki vau4eri po dogovor v ServiceKredits kato usluga "VTD"
  set @CreditNo=GetCounter('CreditCounter');
  insert into ServiceCredits( DateCredit,GuestNo,Service,SumCredit,KodCurrency,KodAdmin,NumberServices,CreditNo,PayedServices,WhoPays,GroupPays,ContractPays,RoomPays,PBXLogN,DateCreditDt,RoomNum) 
    select @InvoiceDate,0,ServiceNo,sum(GrandTotal),@SmetkaCurrency,@KodAdmin,1,@CreditNo,1,0,0,@ContractNo,0,null,FloatToDate(@InvoiceDate),null from
      #VaucherArticles where
      VaucherN <> 0
      group by ServiceNo;
  //Insertva zapis vuv Articles za suma s vaucheri po dogovor
  message 'Faktura('+cast(@TrueInvoiceNo as varchar)+')' type info to console;
  for f0 as curs0 scroll cursor for
    select 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,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,ForVaucherN as @ForVaucherN from
      #VaucherArticles where
      VaucherN <> 0 order by
      VaucherN asc do
    message 'GOST >> ' || @TranslateGuestName type info to console;
    insert into VaucherArticles( InvoiceNo,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,ForVaucherN) values( 
      @InvoiceNo,@ArtNum,@VaucherNo,@VaucherNum,@Seasson,@BoardPlan,@Room,@AdditionBed,@Adult,@Child,@Nights,@TotalNights,
      @RealPrice,@GrTotal,@ArticleName,@GuestName,@CreditNo,@Disc,@ServiceNum,@SmetkaCurrency,@TranslateName,@TranslateSeason,@TranslateBed,@TranslateGuestName,
      @SeassonNo,@HumanKind,@AdultType,@HasExtraBed,@ValBoardPlan,@RoomType,@ForVaucherN) ;
    set @ArtNum=@ArtNum+1;
    message 'COUNTERA('+cast(@ArtNum as varchar)+')' type info to console end for;
  message 'IZLIZA OT PYRVIQ FOR...' type info to console;
  for f2 as curs2 scroll cursor for
    select v.VaucherN as @Vaucher from
      #VaucherArticles as va left outer join
      VtdVauchers as v on
      (v.VaucherN = va.VaucherN) where
      v.VaucherN is not null and
      v.ContractNo = @ContractNo
      group by v.VaucherN do
    message 'Dogovor nomer = '+cast(@ContractNo as varchar) type info to console;
    if @BillType <> -1 then
      update VtdVauchers set TrueInvoceNo = @TrueInvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher;
      update VtdVauchers set InvoiceNo = @InvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher;
    else
      update VtdVauchers set TrueInvoceNo = 0,CancelInvoiceNo = @CancelInvoiceNo where ContractNo = @ContractNo and VaucherN = @Vaucher;
      update VtdVauchers set InvoiceNo = 0 where ContractNo = @ContractNo and VaucherN = @Vaucher;
    end if end for;
  message 'Step2' type info to console;
  for f3 as curs3 scroll cursor for
    select SinglePrice as @ArticlePrice,GrandTotal as @ArticleSum,TotalNights as @ArticleQty,ArticleName as @ArticleName,Discount as @Discount,
      ServiceNo as @Service,TranslateName as @TranslateName,TranslateSeason as @TranslateSeason,
      TranslateBed as @TranslateBed,ForVaucherN as @ForVaucherN from
      #VaucherArticles where(VaucherN = 0) and(Creditno is not null) do
    message 'Step3' type info to console;
    insert into VaucherArticles( InvoiceNo,ArticleNo,VaucherN,
      VaucherNum,VaucherSeason,VaucherBoard,VaucherRoomType,VaucherBedType,Adult,Child,Nights,TotalNights,SinglePrice,GrandTotal,ArticleName,CreditNo,Discount,ServiceNo,Currency,TranslateName,TranslateSeason,TranslateBed,ForVaucherN) values( 
      @InvoiceNo,@ArtNum,'','','','','','',0,0,@ArticleQty,@ArticleQty,
      @ArticlePrice,@ArticleSum,@ArticleName,0,@Discount,@Service,@SmetkaCurrency,@TranslateName,@TranslateSeason,@TranslateBed,@ForVaucherN) ;
    set @ArtNum=@ArtNum+1 end for;
  //Ako e anulirane na smetka i anuliranata smetka ima slujebni zapisi za markirane na depositi gi iztriva
  //Promenia CancelInvoiceNo na anuliranata smetka na -1
  message 'Step4' type info to console;
  if @BillType = -1 then
    message 'MINAVA OT TUKA...' type info to console;
    delete from DBA.ResMoney where VaucherInvoiceNo = @No and DateMoney = 0;
    update VaucherInvoices set CancelInvoiceNo = -1 where VaucherInvoiceNo = @No;
    message '@ResMoneyStatus -> '+cast(@No as varchar) type info to console;
    select max(Status) into @ResMoneyStatus from ResMoney where VaucherInvoiceNo = @No;
    message '@ResMoneyStatus -> '+cast(@ResMoneyStatus as varchar) type info to console
  end if;
  //Nulira depositi, ako e smetka po dogovor
  if(@BillType = 7) and(@ContractNo <> 0) then
    call CheckContractDeposits(@ContractNo,@KodAdmin,@InvoiceNo,@NullDepositCount)
  end if;
  //Vmukva plashtaniata ot wremennata tablica za plashtania
  for f4 as curs4 scroll cursor for
    select PaymentType as @PaymentType,Sum as @Sum,KodCurrency as @KodCurrency,AccountNumber as @AccountNumber,
      GuestNo as @mGuestNo,GroupNo as @mGroupNo,RoomNo as @mRoomNo,ReserveNo as @mReserveNo,PTVid as @PTVid from
      #PaymentTypes do
    set @DepositNo=GetCounter('DepositCounter');
    message 'GostNomer -> '+cast(@mGuestNo as varchar) type info to console;
    insert into ResMoney( ReserveNo,DateMoney,KodAdmin,Sum,Currency,Payment,Note,GuestNo,DepositNo,Status,GroupNo,
      ContractNo,VaucherInvoiceNo,RoomNo,AccountNumber,PTVid) values( 
      @mReserveNo,@InvoiceDate,@KodAdmin,@Sum,@KodCurrency,@PaymentType,'',@mGuestNo,@DepositNo,@ResMoneyStatus,@mGroupNo,@ContractNo,@InvoiceNo,@mRoomNo,@accountNumber,@PTVid) ;
    message 'Minava prez depozitite ' type info to console end for;
  return(@InvoiceNo)
end

GO


if (select count(*) from VidSmetki where SmetkaIme='Хотелска сметка ваучер' and SmetkaFile='VtdHotelSmetka.qri'
     and SlujSmetka=4 and KodCurrency=1)=0 then

insert into VidSmetki(N,SmetkaIme,KodCurrency,SmetkaFile,IsFaktura,SvoService,TextPrint,SlujSmetka,Language)
               values((select MAX(N+1) from VidSmetki),'Хотелска сметка ваучер',1,'VtdHotelSmetka.qri',0,0,0,4,0);

end if;